Jump to content


Photo

Subquery Workaround


  • Please log in to reply
2 replies to this topic

#1 dannyo101

dannyo101
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 13 May 2006 - 08:03 PM

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:

$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>";
}

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!

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 13 May 2006 - 09:43 PM

If a team_id in table sv_pick is associated with only one entry_id then you can try the following query.
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
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.
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)
You could also execute the subquery first creating the list of team_ids to exclude and then run the query using that list
$list=mysql_query("SELECT t.name FROM team t WHERE t.team_id not in ($comma_delimitted_list)");


#3 dannyo101

dannyo101
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 13 May 2006 - 11:43 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users