A simple SQL question about getting rows and related counts

Question

It should be easy.

My question is very similar to this ; basically, I have a message table, a comment table with a foreign key for post_id and a vote table with a foreign key for a post id. I would like to make one request and get a result set containing one line per message, as well as the number of related comments and votes.

From the question I linked to above, it seems that to get a table containing only a row for each post and comment counter, this is the correct approach:

SELECT a.ID, a.Title, COUNT(c.ID) AS NumComments
FROM Articles a
LEFT JOIN Comments c ON c.ParentID = a.ID
GROUP BY a.ID, a.Title

I thought adding the number of votes would be as simple as adding another left join as in

SELECT a.ID, a.Title, COUNT(c.ID) AS NumComments, COUNT(v.id AS NumVotes)
FROM Articles a
LEFT JOIN Comments c ON c.ParentID = a.ID
LEFT JOIN Votes v ON v.ParentID = a.ID
GROUP BY a.ID, a.Title

but again I get bad numbers. What am I missing?

Answer 1

SELECT 
  a.ID, 
  a.Title, 
  COUNT(DISTINCT c.ID) AS NumComments, 
  COUNT(DISTINCT v.id) AS NumVotes
FROM 
  Articles           a
  LEFT JOIN Comments c ON c.ParentID = a.ID
  LEFT JOIN Votes    v ON v.ParentID = a.ID
GROUP BY 
  a.ID, 
  a.Title

Answer 2

SELECT  id, title,
        (
        SELECT  COUNT(*)
        FROM    comments c
        WHERE   c.ParentID = a.ID
        ) AS NumComments,
        (
        SELECT  COUNT(*)
        FROM    votes v
        WHERE   v.ParentID = a.ID
        ) AS NumVotes
FROM    articles a

Answer 3

try: COUNT (DISTINCT c.ID) AS NumComments

Answer 4

You are thinking about trees, not record sets.

In the recordset, you receive each Comment, and each vote is returned several times in combination with each other. Run the query without the by and count group to see what I mean.

The solution is simple: use COUNT (DISCTINCT c.ID) and COUNT (DISTINCT v.ID)