[Mike007]

Hi, I am trying to get rid of some duplicates in this query:

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.

[P4L]

Mike007, on 26 Jun, 2009 - 11:53 AM, said:

Hi, I am trying to get rid of some duplicates in this query:

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.

Most likely it's your WHERE statement. I am not sure if in SQLite that you can use BETWEEN, but for a date range, I would try using

Where date Bewteen 1273122000000 and 1273208340000 
OR date IS NULL

try that, and see what happens. Other than that, everything else looks normal.

This post has been edited by P4L: 26 June 2009 - 01:08 PM

[Mike007]

They do, but this is not what I was trying to do, i was looking for records outside that range. I used date NOT BETWEEN ... but that gave me the same result as before.

EDIT: Ok I got it! You were in the right direction. I was just looking for distinct records that did not include the date. So I just removed date field from the selection part. Thank you for the quick reply however, it is much appreciated.

This post has been edited by Mike007: 26 June 2009 - 01:18 PM