Log in

No account? Create an account

Previous Entry | Next Entry

Apeal to the SQL Guru's

Ok, so I spent a couple hours getting about 30 entries in the database I set up so that I could start playing with the data and making sure it's working correctly.

It's pulling all the data from the DB correctly, but for some reason the following SQL Select Statement

SELECT Album.Album_Name, Artist.Artist_Name, Dj.Dj_Name, Genre.Genre_Name, Night.Night_Name, Play_Tracking.Request, Club.Venue_Name, Track.Track_Name, Track.Rating 

FROM Play_Tracking INNER JOIN Dj ON Play_Tracking.Dj_ID = Dj.Dj_ID 
INNER JOIN Track ON Play_Tracking.Track_ID = Track.Track_ID INNER JOIN Night ON Play_Tracking.Night_ID = Night.Night_ID 
INNER JOIN Record_Label ON Track.Record_Label_ID = Record_Label.Record_Label_ID 
INNER JOIN Club ON Play_Tracking.Club_ID = Club.Club_ID INNER JOIN Album 
INNER JOIN Artist ON Album.Artist_ID = Artist.Artist_ID 
INNER JOIN Genre ON Album.Genre_ID = Genre.Genre_ID ON Track.Artist_ID = Artist.Artist_ID AND Track.Album_ID = Album.Album_ID

Is only pulling 3 entries out of the DB?

Any thoughts?

**note this is the raw data dump of every field in the DB, I did it this way, because the tuned select statement for the data I was needing, was only bringing up three entries.**


( 6 comments — Leave a comment )
Nov. 23rd, 2006 10:05 pm (UTC)
...which makes no sense to me at all, and the German guy in the blue thong is absurd...
Nov. 23rd, 2006 10:10 pm (UTC)
perhaps your data is being eliminated through the inner joins.

are you sure that you don't need outer joins on some of that data?

here's a suggestion:

eliminate all but two tables from the query, make sure you're getting what you are supposed to be getting, and slowly add each join back in.
Nov. 23rd, 2006 10:12 pm (UTC)
kewlio, thanks, I'll give that a try.

Nov. 24th, 2006 02:17 am (UTC)
Well, INNER JOINs will only find results where every one of those relationships exists. E.g., if you have NULL Genre_IDs for any particular album you won't get it back because you've INNER JOIN'd on Genre.

If you make those LEFT [OUTER] JOINs you'll get back everything, though maybe this is a problem with your data set.
Nov. 24th, 2006 10:40 pm (UTC)
Re: NULLs?
no, the problem is with the Joins, Genre is a required field on all track entries. But I've used way too many intter joins (What I get for using MS's query builder in MS Visual Studio) I won't get to play with it much today, but tomorrow at work I get to go to town.

Nov. 25th, 2006 01:43 am (UTC)
Re: NULLs?
I'd do like Jerry said above, change your SQL target to just count(*) and add a table at a time joinwise, and you'll be able to see pretty easily on which relationship your records are getting lost.
( 6 comments — Leave a comment )