How to get count of two fields from two different table with grouping a field from another table in mysql

Question

I have three tables projects, discussions, and comments.

I have tried it like this:

SELECT p.PRO_Name, COUNT( d.DIS_Id ) AS nofdisc, COUNT( c.COM_Id ) AS nofcom 
FROM projects p
LEFT JOIN discussions d ON p.PRO_Id = d.PRO_Id
LEFT JOIN comments c ON d.DIS_Id = c.DIS_Id
GROUP BY p.PRO_Name LIMIT 0 , 30 

But it's taking all the rows from discussions and the count of comments is the same as the count of discussions.

Answer 1

count counts the number of non-null values of the given parameter. The join you have will create a row per comment, where both dis_id and com_id are not null, so their counts would be the same. Since these are IDs, you could just count the distinct number of occurrences to get the response you'd want:

(EDIT: Added an order by clause as per the request in the comments)

SELECT    p.PRO_Name, 
          COUNT(DISTINCT d.DIS_Id) AS nofdisc,
          COUNT(DISTINCT c.COM_Id) AS nofcom 
FROM      projects p 
LEFT JOIN discussions d ON p.PRO_Id = d.PRO_Id 
LEFT JOIN comments c ON d.DIS_Id = c.DIS_Id 
GROUP BY  p.PRO_Name
ORDER BY  2,3 
LIMIT     0 , 30