Mysql, NOT EXISTS, SELECT

Question

Well, I was trying to select rows from one table if there are no rows in another table.

My original query was:

SELECT * FROM `jos_datsogallery` as a WHERE a.published = 1 
and a.approved=1 NOT EXISTS (SELECT * FROM `jos_datsogallery_votes`
As v WHERE v.vip=62 AND v.vpic=a.id) ORDER BY a.imgdate DESC

but it keeps failing.

I made some testing and shortened my query to:

SELECT * FROM `jos_datsogallery` WHERE EXISTS (SELECT 1)

Which is supposed to select everything from jos_datsogallery as 'EXISTS (SELECT 1)' is always true.

I tried phpMyAdmin:

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 1) LIMIT 0, 30' at line 1

What's wrong?

MySQL version: 4.0.27

MySQL doc: http://dev.mysql.com/doc/refman/4.1/en/exists-and-not-exists-subqueries.html

Answer 1

EXISTS is only supported in 4.1 and above - the documentation you linked is a combined documentation for both 4.0/4.1 so it may be misleading as to what versions actually support the keyword.

Since you updated your question to state that you're using 4.0.x that would be why it's not working for you.

Answer 2

Here is another way you can achieve the same, without using NOT EXISTS.

SELECT * FROM `jos_datsogallery` AS a 
LEFT JOIN `jos_datsogallery_votes` AS v ON v.vip=62 AND v.vpic=a.id
WHERE a.published = 1 AND
a.approved=1 AND
v.vip IS NULL
ORDER BY a.imgdate DESC

Using a left join means the right-hand of the join (the jos_datsogallery_votes part) is allowed to not find any rows while still returning a result. When the right hand side of the join is not found, its columns will all have a value of NULL, which you can check on in the WHERE part of the query.

HTH

Answer 3

A little late, but I was searching for something similar and it might be of use to someone else. Another way to do this, is to use a count in the where clause. So, your query would be:

SELECT * FROM `jos_datsogallery` AS a 
WHERE a.published = 1 
AND a.approved=1 
AND (
    SELECT COUNT(*) FROM `jos_datsogallery_votes` AS v 
    WHERE v.vip=62 
    AND v.vpic=a.id
) = 0 
ORDER BY a.imgdate DESC