Jump to content

Archived

This topic is now archived and is closed to further replies.

dannyo101

Subquery Workaround

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Thanks for your help. I went with the second example you listed and it works for what I need. I appreciate it!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.