getting count from 2 tables thare are seperatly related to another tab

[gtjr92]

I have 3 tables Albums, Photos, Videos
The albums table is related to the videos table by the albumid in the video table. The album table is also related to the photo table by the albumid field.
I want to get the count of the videoid's that are related to the albumid and also a seperate unrelated count of the Photoid count that is in the albumid
If I run a quey like this by itself

SELECT COUNT(Videos.VideoID) AS vidscount, Albums.AlbumID, Albums.Caption, Albums.AlbumCategoryID
                    
FROM      Albums INNER JOIN
         Videos ON Albums.AlbumID = Videos.AlbumID
GROUP BY Albums.AlbumID, Albums.Caption, Albums.AlbumCategoryID, Videos.AlbumID

That works I get the count of the videos. I run a similir query and get the count of photoid in each album id.
So I tried to run one query and get the counts of the photo id's and video id. I tried this

SELECT 
		[Albums].[AlbumID],
		[Albums].[AlbumCategoryID],
		[Albums].[Caption], 
		[Albums].[IsPublic], 
		Count([Photos].[PhotoID]) AS NumberOfPhotos,
        Count([Videos].[VideoID]) As NumberofVideos 
	FROM [Albums] LEFT JOIN [Photos] 
		ON [Albums].[AlbumID] = [Photos].[AlbumID] 
	LEFT JOIN [Videos] 
		ON [Albums].[AlbumID] = [Videos].[AlbumID] 
	
	GROUP BY
		[Albums].[AlbumCategoryID],
		[Albums].[AlbumID], 
		[Albums].[Caption], 
		[Albums].[IsPublic]

This ends up giving me the same count for the countofphotos as the countofvideos. ie it says i have 20 videos in an album and 20 photos when really it is just giving me the photos count in both the video and photo counts results. One last thing there is a field thumbnailid in the photo table that is related to the thumbnailid column in videos. This field is not relevent to the data i need.

 

[gtjr92]

Oh one more thing if there are 0 videoid in an album and there is any number of photos in the same albumid then the video count is 0 as it should be and the photo count is also correct.

 

[gtjr92]

And if there are 0 Photoid related to an album and there is any number of videos in the same albumid then the photo count is 0 as it should be and the video count is also correct

 

[vongrunt]

Two ways:

-- 1
select A.AlbumID, A.Caption, A.AlbumCategoryID, A.IsPublic,
	(	select count(P.PhotoID) from Photos P where P.AlbumID=A.AlbumID ) as NumberOfPhotos,
	(	select count(P.VideoID) from Videos V where V.AlbumID=A.AlbumID ) as NumberOfVideos
from Albums A	

-- 2
select A.AlbumID, A.Caption, A.AlbumCategoryID, A.IsPublic,
	isnull(P.cnt, 0) as NumberOfPhotos,
	isnull(V.cnt, 0) as NumberOfVideos
from Albums A	
left outer join
(	select AlbumID, Count(PhotoID) as cnt
	from Photos
	group by AlbumID
) P
on A.AlbumID = P.AlbumID
left outer join
(	select AlbumID, Count(VideoID) as cnt
	from Videos
	group by VideoID
) V
on A.AlbumID = V.AlbumID

1st is simpler, 2nd scales better and is more extensible

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[gtjr92]

thanks worked like a charm