Getting NULL rows with multiple joins

[LoveToCode]

For the past couple days I've been trying to get this query to work correctly. I just re-wrote it to be more human readable, to help me debug it, but I'm still getting problems.

The main problem now is that 4 out of the 6 rows have every column completely null.

alter procedure [dbo].[sp_someSite_user_playlist_getSongs]        
        
@playlistId int        
        
as         

SELECT 

-- MP3 table
Mp3.Mp3Id, Mp3.Mp3Name, Mp3.Mp3Src, Mp3.Artist, Mp3.TrackTitle, Mp3.Listens, Mp3.UserId, Mp3.DateAdded, Mp3.GenreId,
-- Artist
Artist.[name] ArtistName, Artist.ArtistId ArtistId, 
-- ID3
ID3.BitRate, ID3.Mode, dbo.SecToMin(ID3.length) Length,
-- Album
Album.ImageSrc

 FROM 
	someSite_user_playlist_songs PL

LEFT JOIN
	someSite_mp3 Mp3 
	ON PL.Mp3Id = Mp3.Mp3Id

LEFT JOIN
	theSite_artist Artist 
	ON Mp3.UserId = Artist.ArtistId

LEFT JOIN
	theSite_mp3_id3 ID3 
	ON Mp3.Mp3Id = ID3.Mp3Id

LEFT JOIN
	theSite_artist_album Album 
	ON Mp3.AlbumId = Album.AlbumId

WHERE
PL.PlaylistId=@playlistId

Thank you for looking at my query!

-- Jenni

 

[gmmastros]

You would get rows where every column is completely null when a item is in the PlayList but doesn't exist in any of the other tables.

I would suggest that you change the left join on the MP3 table to an inner join.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Let me explain this a little better. There must be records in the PL table that do not have corresponding records in the MP3 table.

If you would add columns from the PL table to the select list, you would see what I mean.

It appears as though the PL table is joined to the mp3 table. Then the mp3 table is joined to the Artist, id3, and album table. By changing the Left join to an inner join (between the PL table and mp3 table), you won't get any records where every column is null.

This is a perfect example of orphaned records. (hint: do a google search). This is also the reason why foreign keys were invented. Someone must have added songs to their playlist and then deleted the MP3 record, leaving a record in the play list that doesn't point to a valid song in the mp3 list.

Does this make better sense now? I hope so, but if it doesn't, let me know and I will explain more.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LoveToCode]

Cool, that did work! Though now, its showing only 2 songs in a playlist that has a SongCount of 6... which I guess means that somewhere, like you said, a users is delete songs, but the aren't getting removed in the proper location.

Foreign Keys!!! They're good fer something huh?? Lol! Well I think I will have to revisit the database schema and add FKs to all these tables. I don't what I was thinking.

Thanks for the great explanation! I really appreciate it.

-- Jenni