CODEWITHSUNDEEP
sqlmysql
undsoft
undsoft

Reputation: 818

Mysql, NOT EXISTS, SELECT

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

Upvotes: 0

Views: 3495

Answers (3)

Bas
Bas

Reputation: 3096

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

Upvotes: 1

Aistina
Aistina

Reputation: 12683

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

Upvotes: 2

Amber
Amber

Reputation: 526523

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.

Upvotes: 5

Related Questions