What's the best T-SQL syntax to filter for an ID that has a count of X or at least X or at most X in a joined table?

Question

What's the best way to do something like this in T-SQL?

SELECT DISTINCT ID
FROM Members,
INNER JOIN Comments ON Members.MemberId = Comments.MemberId
WHERE COUNT(Comments.CommentId) > 100

Trying to get the members who have commented more than 100 times. This is obviously invalid code but what's the best way to write this?

Answer 1

This should get you what you're after. I'm not saying this is the absolutely best way of doing it, but it's unlikely you'll find anything better.

SELECT ID
FROM   Members
INNER JOIN Comments
ON Members.MemberId = Comments.MemberId
GROUP BY ID
HAVING COUNT(*) > 100

Answer 2

I like using a subquery.

SELECT DISTINCT m.ID
FROM Members m
WHERE (SELECT COUNT(c.CommentID)
       FROM Comments c
       WHERE c.MemberID = m.MemberID) > 100

Answer 3

Try

SELECT ID
FROM Members
INNER JOIN (SELECT MemberID FROM Comments 
     GROUP BY MemberID HAVING COUNT(CommentId) > 100) 
AS CommentCount ON Members.MemberID = CommentCount.CommentID