Have Geek Brain

Checking for missing MySQL database data

I have my songs setup in my MySQL database.

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

Only those songs should be pulled from the database that either do not have the places assigned in the database, or have the place assigned to the ones below.

Hopefully when you understand my query below it makes sense

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 that it fetches from the database songs that are assigned to sl.location_id that are not listed 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?

+2


a source to share


1 answer


If the location is not linked to a location it sl.location_id

will null

, so just add it to WHERE

:



SELECT s.* FROM roster_songs AS s 
LEFT JOIN roster_songs_locations AS sl ON sl.song_id = s.id 
WHERE
   sl.location_id = NULL
   OR  
   sl.location_id = '88fb5f94-aaa6-102c-a4fa-1f05bca0eec6' 
   OR 
   sl.location_id = '930555b0-a251-102c-a245-1559817ce81a'
) 
GROUP BY s.id
+1


a source






More articles:


All Articles