Exists

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Shendemiar
Forum Contributor
Posts: 404
Joined: Thu Jan 08, 2004 8:28 am

Exists

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

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 »

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 »

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 »

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
Post Reply