Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Shendemiar
Forum Contributor
Posts: 404 Joined: Thu Jan 08, 2004 8:28 am
Post
by Shendemiar » Mon Jan 19, 2004 8:42 am
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
twigletmac
Her Royal Site Adminness
Posts: 5371 Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK
Post
by twigletmac » Mon Jan 19, 2004 8:44 am
Your version of MySQL is? Subqueries are only available in 4.1 or above.
Mac
Shendemiar
Forum Contributor
Posts: 404 Joined: Thu Jan 08, 2004 8:28 am
Post
by Shendemiar » Mon Jan 19, 2004 8:49 am
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...
Shendemiar
Forum Contributor
Posts: 404 Joined: Thu Jan 08, 2004 8:28 am
Post
by Shendemiar » Mon Jan 19, 2004 8:51 am
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;
Shendemiar
Forum Contributor
Posts: 404 Joined: Thu Jan 08, 2004 8:28 am
Post
by Shendemiar » Mon Jan 19, 2004 8:55 am
I just dont understan <span style='color:blue' title='I'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