Join 3 tables, count records in 2 of them

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Join 3 tables, count records in 2 of them

Author

Topic

Desmag
Starting Member

18 Posts

Posted - 2005-10-27 : 14:46:24

Hi everybody,

Im builbing a gallery :)

I have 3 tables:
tbl_Gallery (main table),
tbl_Gallery_Comment (for comments on photos),
tbl_Gallery_ImgViews (list of clicks on pictures).

I need to write a qry which picks ID from the main table, counts number of comments on each image from 2nd table, and counts the number of clicks from the 3rd table.

So far I have


SELECT 
    G.Img_Id,  
    G.Img_Title,  
    COUNT(GC.Comment_Image_Id) AS CountOfComments, 
    COUNT(GV.Img_Id) AS CountOfImpressions

FROM tbl_Gallery G 
LEFT JOIN 
    tbl_Gallery_Comment GC 
    ON G.Img_Id = GC.Comment_Image_Id
LEFT OUTER JOIN 
    tbl_Gallery_ImgViews GV 
    ON G.Img_Id = GV.Img_Id
 
WHERE G.Img_Category = @Gallery_Id 
GROUP BY G.Img_Id, 
    G.Img_Title

ORDER BY G.Img_Id DESC

Above code gives me the wrong count on both count columns, but if I split this qry into 2, then both of them work just fine giving right values...

Could somobody help me with this?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-28 : 01:47:58

Try:

    COUNT(DISTINCT GC.[Primary Key of table]) AS CountOfComments, 
    COUNT(DISTINCT GV.[Primary key of table]) AS CountOfImpressions

--or--

select	tbl_Gallery.Img_Id,  
	tbl_Gallery.Img_Title,  
	CommentCounts.CountOfComments,
	ImpressionCounts.CountOfImpressions
from	tbl_Gallery
left outer join --CommentCounts 
	(select	Comment_Image_Id,
		count(*) as CountOfComments
	from	tbl_Gallery_Comment
	group by Comment_Image_Id) CommentCounts
	on tbl_Gallery.Img_Id = CommentCounts.Comment_Image_Id
left outer join  --ImpressionCounts
	(select	Img_ID,
		count(*) as CountOfImpressions
	from	tbl_Gallery_ImgViews
	group by Img_ID) ImpressionCounts
	on tbl_Gallery.Img_Id = ImpressionCounts.Img_Id
where	tbl_Gallery.Img_Category = @Gallery_Id 
order by tbl_Gallery.Img_Id desc

(Notice how much easier to understand the code is without the superfluous and sadly ubiquitous acronymic table aliases?...)

Desmag
Starting Member

18 Posts

Posted - 2005-10-28 : 05:44:00

It has worked :D

Thank you very much blindman!

PS. I see your point regarding table aliases, ill paste the the full code next time.

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-28 : 10:53:37

And ain't it amazing....you think doing this stuff with a Braille keyboard is easy....

http://deafandblind.com/braille-keyboard.html

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-28 : 11:16:04

quote:
(Notice how much easier to understand the code is without the superfluous and sadly ubiquitous acronymic table aliases?...)

I'll defend your right to believe so, although I think it's hogwash!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-28 : 11:36:17

W M you T it is H? S A in the N of R TYP S2 INV C MSTK and LOG E.

mistakes as MSTK
logical as LOG
errors as E
What as W
Superfluous as S
aliases as A
hogwash as H
name as N
reducing as R
typing as TYP
simply as S2
makes as M
think as T
invite as INV
coding as C

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-28 : 12:17:12

I'd might agree if the formatting was cleaner


	 SELECT	   tbl_Gallery.Img_Id  
		 , tbl_Gallery.Img_Title  
		 , CommentCounts.CountOfComments
		   ImpressionCounts.CountOfImpressions
	   FROM	   tbl_Gallery
LEFT OUTER JOIN (  SELECT   Comment_Image_Id
			  , COUNT(*) AS CountOfComments
		     FROM   tbl_Gallery_Comment
		 GROUP BY Comment_Image_Id
		) AS CommentCounts
	     ON    tbl_Gallery.Img_Id = CommentCounts.Comment_Image_Id
LEFT OUTER JOIN (  SELECT   Img_ID
			  , count(*) AS CountOfImpressions
		     FROM   tbl_Gallery_ImgViews
		 GROUP BY Img_ID
		) AS ImpressionCounts
	     ON    tbl_Gallery.Img_Id = ImpressionCounts.Img_Id
	  WHERE	   tbl_Gallery.Img_Category = @Gallery_Id 
       ORDER BY    tbl_Gallery.Img_Id DESC

But I'd still prefer:


	 SELECT	   g.Img_Id  
		 , g.Img_Title  
		 , c.CountOfComments
		   i.CountOfImpressions
	   FROM	   tbl_Gallery g
LEFT OUTER JOIN (  SELECT   Comment_Image_Id
			  , COUNT(*) AS CountOfComments
		     FROM   tbl_Gallery_Comment
		 GROUP BY Comment_Image_Id
		) AS c
	     ON    g.Img_Id = c.Comment_Image_Id
LEFT OUTER JOIN (  SELECT   Img_ID
			  , count(*) AS i
		     FROM   tbl_Gallery_ImgViews
		 GROUP BY Img_ID
		) AS i
	     ON    g.Img_Id = i.Img_Id
	  WHERE	   g.Img_Category = @Gallery_Id 
       ORDER BY    g.Img_Id DESC

I mean blind dude, it's only three tables (1 physical, 2 derived)

(This is one of his favorite debates)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-28 : 13:11:35

I'm out to change the world.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-28 : 13:15:14

quote:

But I'd still prefer:


	 SELECT	   g.Img_Id  
		 , g.Img_Title  
		 , c.CountOfComments
		   i.CountOfImpressions
	   FROM	   tbl_Gallery g
LEFT OUTER JOIN (  SELECT   Comment_Image_Id
			  , COUNT(*) AS CountOfComments
		     FROM   tbl_Gallery_Comment
		 GROUP BY Comment_Image_Id
		) AS c
	     ON    g.Img_Id = c.Comment_Image_Id
LEFT OUTER JOIN (  SELECT   Img_ID
			  , count(*) AS i
		     FROM   tbl_Gallery_ImgViews
		 GROUP BY Img_ID
		) AS i
	     ON    g.Img_Id = i.Img_Id
	  WHERE	   g.Img_Category = @Gallery_Id 
       ORDER BY    g.Img_Id DESC

Dude, is that SQL code, or is it a Rorschach test? If I squint it looks like two bats having sex.

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-28 : 14:27:38

[quote]Originally posted by blindman

--or--

select	tbl_Gallery.Img_Id,  
	tbl_Gallery.Img_Title,  
	CommentCounts.CountOfComments,
	ImpressionCounts.CountOfImpressions
from	tbl_Gallery
left outer join --CommentCounts 
	(select	Comment_Image_Id,
		count(*) as CountOfComments
	from	tbl_Gallery_Comment
	group by Comment_Image_Id) CommentCounts
	on tbl_Gallery.Img_Id = CommentCounts.Comment_Image_Id
left outer join  --ImpressionCounts
	(select	Img_ID,
		count(*) as CountOfImpressions
	from	tbl_Gallery_ImgViews
	group by Img_ID) ImpressionCounts
	on tbl_Gallery.Img_Id = ImpressionCounts.Img_Id
where	tbl_Gallery.Img_Category = @Gallery_Id 
order by tbl_Gallery.Img_Id desc
[/code]


Looks like someone dropped a bottle of ink on a post to me....

Go Bengals....Don't hurt Brett too much





Brett

8-)

Hint:  Want your questions answered fast?  Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-28 : 14:38:56

Philistine! My code is Concrete Poetry! Uncultured east-coast heathen wouldn't know poetic coding if it jumped up and bit you on your Bengals. Owch.

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-28 : 14:48:14

quote:
Originally posted by blindman
Concrete Poetry!

Funny, I didn't know you are a mason....how long does your code take to dry up (wunderdog)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-28 : 16:31:11

What, you never did Concrete Poetry back in Grade School? Your education is incomplete!
http://www.gardendigest.com/concrete/this.htm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources