Link to home
Start Free TrialLog in
Avatar of sherbug1015
sherbug1015Flag for United States of America

asked on

SQL Server Count Function Misbehaving

I have a query where I need to get two counts from two different tables.  

select a.BlogPostDate,c.CommentPostDocumentID,b.BlogPostID,a.DocumentID,
Count(b.BlogPostID) NumberLikes,
Count(c.CommentPostDocumentID) NumberComments
from View_Blogs_Joined a
join customtable_BlogPostLikes b on a.DocumentID = b.BlogPostID
join Blog_Comment c on a.DocumentID = c.CommentPostDocumentID
group by a.DocumentID,a.BlogPostDate,b.BlogPostID,c.CommentPostDocumentID

What I am getting back for my counts is a  cartesian product.  The View has 4 records only.  The product is coming in when I join both the counter tables to the View on the same field.  I don't have any other candidates for a match other than the DocumentID.  Any ideas on how I can get the two counts.  I tried a union, but I am getting all the counts back in one column where I need two columns.

Thanks in advance.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial