Page 1 of 1

Exists

Posted: Mon Jan 19, 2004 8:42 am
by Shendemiar
Mysql

What am i doing wrong:

Code: Select all

select id from games where exists
(SELECT * FROM  games g, players_games pg where g.id=pg.gid)
the subquery works fine alone.

The error i get is :
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (
SELECT DISTINCT *
FROM games g, players_games pg
W

Posted: Mon Jan 19, 2004 8:44 am
by twigletmac
Your version of MySQL is? Subqueries are only available in 4.1 or above.

Mac

Posted: Mon Jan 19, 2004 8:49 am
by Shendemiar
mysql
MySQL Support enabled
Active Persistent Links 0
Active Links 1
Client API version 3.23.49

So what's the way around it? It then...

Posted: Mon Jan 19, 2004 8:51 am
by Shendemiar
I guess this will do:

The queries:

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2
WHERE table1.id=table2.id);

Can be rewritten as:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL;

Posted: Mon Jan 19, 2004 8:55 am
by Shendemiar
I just dont understan <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> of it but it does the job...

SELECT g.* FROM games g LEFT JOIN players_games pg ON g.id=pg.gid WHERE pg.gid IS NULL