Checking if MySQL Database data does not exist

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
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Checking if MySQL Database data does not exist

Post by Mr Tech »

I have my songs set-up in my MySQL database.

Each song is is either assigned multiple locations or has no locations at all.

Only the songs that either have no locations assigned in the database or have the location assigned to the ones specified below should be pulled from the database.

Hopefully when you understand my query below it will make sense

Code: Select all

    SELECT s.* FROM roster_songs AS s 
        LEFT JOIN roster_songs_locations AS sl ON sl.song_id = s.id 
        WHERE EXISTS (
            SELECT sl2.* FROM roster_songs_locations AS sl2 WHERE s.id != sl2.song_id
        ) OR ( 
           sl.location_id = '88fb5f94-aaa6-102c-a4fa-1f05bca0eec6' 
           OR 
           sl.location_id = '930555b0-a251-102c-a245-1559817ce81a'
        ) 
        GROUP BY s.id
The query almost works except it pulls out of the database songs that are assigned to sl.location_id's that aren't specified in the above query. I think it has something to do with my EXISTS code picking them up...

Any ideas how I can get this to work?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Checking if MySQL Database data does not exist

Post by Eran »

Code: Select all

SELECT s.* FROM roster_songs AS s
LEFT JOIN roster_songs_locations AS sl ON sl.song_id = s.id        
WHERE sl.location_id IS NULL 
OR sl.location_id IN ('88fb5f94-aaa6-102c-a4fa-1f05bca0eec6','930555b0-a251-102c-a245-1559817ce81a')
GROUP BY s.id
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Checking if MySQL Database data does not exist

Post by Mr Tech »

Ah, LEGEND! So many things I still need to learn about MySQL :) Thanks for your help! I was able to use that code for something totally different as well so I killed two birds with one stone ;)
Post Reply