QUOTE(Mike007 @ 26 Jun, 2009 - 11:53 AM)
Hi, I am trying to get rid of some duplicates in this query:
sql
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
CODE
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 Jun, 2009 - 12:08 PM
Forum Index:
Programming Help |
C and C++ |
VB6 |
Java |
VB.NET |
C# |
ASP.NET |
PHP |
ColdFusion |
Perl and Python |
Ruby |
Databases |
Other Languages |
Game Programming |
Mobile Development |
Software Development |
Computer Science |
Industry News |
Programming Tutorials |
C++ Tutorials |
Visual Basic Tutorials |
Java Tutorials |
VB.NET Tutorials |
C# Tutorials |
Linux Tutorials |
PHP Tutorials |
ColdFusion Tutorials |
Windows Tutorials |
HTML/JavaScript Tutorials |
CSS Tutorials |
Flash Tutorials |
Web Promotion Tutorials |
Graphic Design & Photoshop Tutorials |
Software Development Tutorials |
Database Tutorials |
Other Language Tutorials |
ASP.NET Tutorials |
Game Programming Tutorials |
WPF & Silverlight Tutorials |
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.
| 
