Subquery Workaround

[dannyo101]

I'm running MySQL version 4.0.18 and PHP version 4.4.1

I've been struggling with this for most of the day and just figured out that the version of MySQL I'm running does not support subqueries, so I'm trying to come up with a workaround to use in my PHP/MySQL script.

This is the subquery I have:

[code]
$list=mysql_query("SELECT t.name FROM team t WHERE t.team_id not in (SELECT p.team_id from sv_pick p WHERE p.entry_id = 1)") or die("SELECT //Error: ".mysql_error());

echo "Available Teams<br>";
while ($tlist=mysql_fetch_row($list)) {
echo $tlist[0]."<br>";
}
[/code]

Can anyone help me come up with a way around using a subquery. I'm still pretty new to PHP and would appreciate any help.

Thanks!

[shoz]

If a team_id in table sv_pick is associated with only one entry_id then you can try the following query.
[code]
SELECT
t.name
FROM
team AS t
INNER JOIN
sv_pick AS p
ON
t.team_id = p.team_id
WHERE p.entry_id != 1
[/code]
If a team_id is associated with a number of entry_ids one of which could be "1" then you can try the following query.
[code]
SELECT
t.name
FROM
team AS t
LEFT JOIN
sv_pick AS p
ON
t.team_id = p.team_id AND p.entry_id = 1
WHERE ISNULL(p.team_id)
[/code]
You could also execute the subquery first creating the list of team_ids to exclude and then run the query using that list
[code]
$list=mysql_query("SELECT t.name FROM team t WHERE t.team_id not in ($comma_delimitted_list)");
[/code]

[dannyo101]

Thanks for your help. I went with the second example you listed and it works for what I need. I appreciate it!