sherbug1015
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.CommentPo stDocument ID,b.BlogP ostID,a.Do cumentID,
Count(b.BlogPostID) NumberLikes,
Count(c.CommentPostDocumen tID) 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.BlogPostDat e,b.BlogPo stID,c.Com mentPostDo cumentID
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.
select a.BlogPostDate,c.CommentPo
Count(b.BlogPostID) NumberLikes,
Count(c.CommentPostDocumen
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.BlogPostDat
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.