Getting Dup results with a multiple join

[LoveToCode]

In my result set I am seeing double instances of the same song (Mp3Id). It has to be in the logic of my join... does anyone see what couple possibly be wrong

select Mp3.*, Artist.[Name] ArtistName, Album.*,   
Id3.bitRate,  
Id3.frequency,  
Id3.mode,  
dbo.SecToMin(Id3.length) length  
  
from wt_mp3 Mp3      
      
-- get artist info      
inner join wt_artist Artist      
on Mp3.userId = Artist.artistId      
      
-- get album info (per song)      
left join wt_artist_album Album      
on Mp3.userId = Album.artistId      
    
-- get id3 data (length)    
left join wt_mp3_id3 Id3    
on Mp3.mp3Id = Id3.mp3Id      
      
where Mp3.UserId = @artistId

Thank you!

-- jenni

 

[AlexCuse]

First guess would be a duplicate ArtistID in your Artist Table.

Try running this and let us know what you find out.

select artistID, count(artistID)
from wt_artist
group by artistID
order by count(artistID) desc

You can use this query to check for duplicates, and if there are any they will show at the top of your result set.

If there are duplicates, you will need to clean them up before your query will show the proper results.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[LoveToCode]

My result set shows just one of each id:

artistID
----------- -----------
9 1
10 1
11 1
12 1
13 1
14 1
15 1


ArtistID is a Primary key column... so I dont think that is the problem.

- JQ

 

[AlexCuse]

Hm, I am thinking there must be duplication somewhere, and this would have to be on your inner join.

Can you provide some more details on what you're seeing? Most importantly is every song showing up twice, or is there a duplicate record for only a couple songs?

Ignorance of certain subjects is a great part of wisdom

 

[LoveToCode]

This only happens when the artist has two albums or more. In this case there are two albums... but they have unique IDs. Maybe its that relationship?

Here is the result set:

http://wisetopic.com/profileArtist/?artistId=15

Thanks!!

- JQ

 

[AlexCuse]

I think your problem is in this:

left join wt_artist_album Album
on Mp3.userId = Album.artistId

Should you be joining on your unique album ID?

hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[LoveToCode]

Duh! Thanks Alex! I totally missed that I changed this line and it pulls the right records.

-- get album info (per song)      
left join wisetopic_artist_album Album      
on Mp3.albumId = Album.albumId

There is a problem now though, the Image column, plus a bunch of other columns, are all showing up NULL... does this have something to do with the lkind of join?

thanx

 

[AlexCuse]

Yes, because it is a left join, anything that does not have a match on the right table (the table you are joining to) will show up as null. This is different from an INNER join, where the query will only show records that have a match on both sides.

Glad you got it working,

Alex

Ignorance of certain subjects is a great part of wisdom