Alex Rivera | Logout

How to JOIN a COUNT from a table, and then effect that COUNT with another JOIN

Asked 2010-04-10T10:20:04.077
16

I have three tables

Post

ID  Name
1   'Something'
2   'Something else'
3   'One more'

Comment

ID  PostId  ProfileID  Comment
1   1       1          'Hi my name is' 
2   2       2          'I like cakes'
3   3       3          'I hate cakes'

Profile

ID  Approved
1   1          
2   0          
3   1

I want to count the comments for a post where the profile for the comment is approved

I can select the data from Post and then join a count from Comment fine. But this count should be dependent on if the Profile is approved or not.

The results I am expecting is

CommentCount

PostId  Count
1       1
2       0
3       1
sql join count
Edit
Report

1 Answer

1
SELECT Post.Id, COUNT(Comment.ID) AS Count
FROM Post
LEFT JOIN Comment ON Comment.PostId = Post.ID
LEFT JOIN Profile ON Profile.ID = Comment.ProfileID
WHERE Profile.Approved = 1
GROUP BY Post.Id

Probably you didn't paste it for the sake of the example, but you might evaluate to de-normalize the Profile table together with the Comment one, by moving the Approved column in it.

answered 2010-04-10T10:33:45.880

Your Answer