Q:

SQL display records matching lookup table first then all other records

Imagine it is a music site and I have 1000 songs.  Users can "favorite" songs, this is stored in a lookup table using memberID and songID.
I then want to display the favorites for a specific user at the top of a list of all songs.
SELECT  s.*, s.songID theSongID, f.*  
FROM su_songs AS s 
left JOIN su_member_favourites_lookup AS f 
ON f.songID = s.songID 
ORDER by f.songID IS NULL, s.songTitle ASC

...works to display all favorites at the top.  But I just want to display the logged in user's favorites and then all other songs underneath
SELECT  s.*, s.songID theSongID, f.*  
FROM su_songs AS s 
left JOIN su_member_favourites_lookup AS f 
ON f.songID = s.songID 
WHERE memberID = " . $memberID . " 
ORDER by f.songID IS NULL, s.songTitle ASC

...if I add the WHERE clause to match the user, it only shows the user's favorites and leaves out the rest of the songs.

A:

The solution is to use AND instead of WHERE
SELECT  s.*, s.songID theSongID, f.*  
FROM su_songs AS s 
LEFT JOIN su_member_favourites_lookup AS f 
ON f.songID = s.songID AND f.memberID = " . $memberID . " 
ORDER by f.songID IS NULL, s.songTitle ASC

This started as a real question and then I had a thought on what might work...and it did.   Hopefully this will help someone else.

