CODEWITHSUNDEEP
sql
Alan
Alan

Reputation: 399

SQL Query for Finding All Records Without a Certain Value

I have a table with two columns, albumID and photoID.

How would I return a column of all the photoID's that are not in an albumID?

For example, say I have this table and I want to find all the photoID's that are not in album 0.

|albumID|photoID|  
|0      |0      |  
|0      |1      |  
|1      |0      |  
|1      |2      |  
|1      |3      |

Then, I would only want to display photos 2 and 3. Right now, I only have this query: 

SELECT DISTINCT photoID FROM PhotoInAlbum WHERE AlbumID <> 1

which returns photos 0,2,3, but 0 appears in album 0.

Thanks so much!

Upvotes: 2

Views: 6190

Answers (4)

beejm
beejm

Reputation: 2481

SELECT DISTINCT photoID FROM PhotoInAlbum WHERE photoID NOT IN
(
SELECT photoID from PhotoInAlbum WHERE AlbumID = 0
)

Use a subquery

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

Obviously a photo can occur multiple times in the table, but you want to get a list of photos (those that don't exist in album 0) and you probably don't want a photo listed more than once. So aggregate your rows with GROUP BY in order to get one row per photo. Use HAVING to limit your results so to keep only those that are not in album 0.

select photoID
from PhotoInAlbum 
group by photoID
having count(case when albumID = 0 then 1 end) = 0;

Upvotes: 1

Nagesh Katke
Nagesh Katke

Reputation: 578

Use subquery 

SELECT DISTINCT photoID FROM PhotoInAlbum WHERE photoID NOT IN (SELECT albumID FROM PhotoInAlbum)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You could also approach this using a self left-join. It would seem to make sense to also return the albums to which each photo belongs. If you really just want photo IDs, then you can replace my select with SELECT DISTINCT p1.photoID instead.

SELECT p1.albumID, p1.photoID, p2.photoID
FROM PhotoInAlbum p1
LEFT JOIN PhotoInAlbum p2
    ON p1.photoID = p2.photoID AND p2.albumID = 0
WHERE p2.photoID IS NULL;

Output:

enter image description here

Demo here:

Rextester

Upvotes: 1

Related Questions