SELECT DISTINCT Songs.songId AS songId, title, artist, recordingType, recordingTitle, year, playtime, popularity, frequency, date FROM Songs INNER JOIN SongsInfo ON Songs.songId = SongsInfo.songId LEFT JOIN Playlists ON Songs.songId = Playlists.songId WHERE (date <= 1273122000000 OR date >= 1273208340000) OR date IS NULL
What I am trying to do here, is find all the songs that were not added to a playlist on a certian date. That means that they either do not appear in the Playlists table at all, or they have a date that is not in range. There is a one-to-one relationship between Songs and SongsInfo (it just provides some extra information that needs to be kept separate). Songs and Playlists have a one-to-many relationship.
The DISTINCT key word gets rid of only half of the duplicates. What am I doing wrong?
I am using SQLite btw.