2 Replies - 2251 Views - Last Post: 26 June 2009 - 01:11 PM Rate Topic: -----

#1 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Getting Rid of Duplicates in a SELECT Query

Posted 26 June 2009 - 12:53 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Getting Rid of Duplicates in a SELECT Query

#2 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Getting Rid of Duplicates in a SELECT Query

Posted 26 June 2009 - 01:07 PM

View PostMike007, 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

Was This Post Helpful? 0
  • +
  • -

#3 Mike007  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 7
  • View blog
  • Posts: 332
  • Joined: 30-August 07

Re: Getting Rid of Duplicates in a SELECT Query

Posted 26 June 2009 - 01:11 PM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1