This Left Join is not working

[LoveToCode]

This query does NOT return a column called SONG COUNT, as I thought it would when I wrote it. If I run this by itself it returns expected results:

select albumId, count (*) AS SongCount
	from wisetopic_artist_album_mp3
	group by albumId

But this whole query doesn't include that column.. maybe a problem with my join?

select 
	a.*, 
	genre1.Genre as Genre1,
	genre2.Genre as Genre2

from wisetopic_artist_album A

-- get genres
left join wisetopic_mp3_genre genre1 on a.genreId1 = genre1.id
left join wisetopic_mp3_genre genre2 on a.genreId2 = genre2.id

-- get soung count
right join 
 (select albumId, count (*) AS SongCount
	from wisetopic_artist_album_mp3
	group by albumId) C 
	on A.albumId = C.albumId 

where a.albumId=@albumId

Thanks.

 

[bborissov]

This qury doesn't include that column becuase YOU didn't include it

select
    a.*,
    genre1.Genre as Genre1,
    genre2.Genre as Genre2,
    C.SongCount
.....

and BTW Why you need RIGHT JOIN? I think it must be LEFT JOIN.



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LoveToCode]

Daaar! Thanks for pointing out that obvious mistake! Sometimes it takes a second opinion to find stupid problems like that

Yes, you're right. It shoudl be a left join. I was just experimenting, see how the result set would change.

While on this topic, do you happen to know how to replace the SongCount column with 0 if its NULL?

-- Jenni

 

[AlexCuse]

isnull(SongCount, 0)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

If this column is NULL you wouldn't have ANY resultset becuase you self-join the table and want the info for ONE of the albums. BUT hee how you could manage NULLs in some different select:

select
    a.*,
    genre1.Genre as Genre1,
    genre2.Genre as Genre2,
    COALESCE(C.SongCount,0) AS SongCount
-- or ISNULL(C.SongCount,0) AS SongCount

I prefer COALESCE because that function better defines the resulting field type.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[LoveToCode]

Cool, thanks guys! I haven't even heard of COALESCE before, but now I know. And knowing is half the battle.

-- Jenni