Joining 2 tables
-
Hi guys,
I have a table called category with 6 records.
CategoryID Name
1 AA
2 BB
3 CC
4 DD
5 EE
6 FF
and another table where I store all comments for each category.
CommentID
Comment
CategoryID
as you can see the link field in this table called comment is called CategoryID
what I'm trying to accomplish is having a webpage where it's gonna show all categories and next to the categoryname a sum of how many comments are for each category.
Something like
AA 1 comment
BB 0 comment
CC 3 comment
DD 0 comment
EE 5 comment
FF 0 comment
Can this be accomplished in one query?
Thx a lot
-
Do you mean something like this?
Select C.CategoryID, C.name, count(*) as Total from dbo.Category C LEFT OUTER JOIN dbo.Comments CM on C.CategoryID = CM.CategoryID
GROUP BY C.CategoryID, C.name
ORDER BY C.name
-
yes, just I forgot to mention something that is changing the results.
in the comment table I have an userid and I want to show for the specific users, the sum of his comments for each category.
So if I put the condition in your query where userid = 1 then only the categories where users entered comments will show up.
How can I change the query in order to show all categories for that user, even the one with 0 comments.
Thx in advance
-
Select C.CategoryID, C.name, count(*) as Total from dbo.Category C LEFT OUTER JOIN dbo.Comments CM on C.CategoryID = CM.CategoryID and CM.UserId = @userid
GROUP BY C.CategoryID, C.name
ORDER BY C.name
-
On a last note, and outer join (left, right or cross) with a where condition like : where SomeCol = @SomeValue basically becomes an inner join if the search criteria is in the outer table. This is because when no hit is found, a null value will be placed for all non-matches and that null = @something will always return "unknown" therefore filtering the row out of the resultset.
-
Your solution is the perfect one.
Thanks a lot Remi Gregoire!
-
HTH.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply