Query count of children, grandchildren of each grandfather by joining 3 SQL tables-CodePudding

I have the following 3 tables:

Parents table

id   names
------------
1     john
2     William

Children table

id  parent_id   names
----------------------
1     1          Thomas
2     1          Alfie
3     2          Henry

Grandchildren table which points to Children table

id  parent_id   names
----------------------
1     3          Harry
2     1          Joseph
3     1          Adam

I want to get the names of each grandfather with respective count of children and count of grandchildren.

I tried the following, but got wrong counts:

SELECT Parents.Id
      ,Parents.[name]
      ,COUNT(Children.Id) as children
      ,COUNT(Grandchildren.Id) as grandchildren 
  FROM Parents
  LEFT JOIN Children 
    ON Parents.Id = Children.ParentId
  LEFT JOIN Grandchildren
    ON Children.Id = Grandchildren.ParentId
  GROUP BY Parents.Id
          ,Parents.[name]

What may I be doing wrong?

CodePudding user response：

You should count the distinct children for each parent because the joins may return more than 1 row for each child.
As it is your query there is no need to count distinct grandchildren but if you decide to add more joins then you should also do that too.

In any case use the keyword DISTINCT (and aliases for the tables to make the code more readable):

SELECT p.Id, p.[name], 
       COUNT(DISTINCT c.Id) children, 
       COUNT(DISTINCT g.Id) grandchildren 
FROM Parents p
LEFT JOIN Children c ON p.Id = c.ParentId
LEFT JOIN Grandchildren g ON c.Id = g.ParentId
GROUP BY p.Id, p.[name];

CodePudding user response：

I think you are just missing a DISTINCT keyword . your query should be like

select  Parents.Id, Parents.[name] , COUNT( DISTINCT Children.Id ) children, count(Grandchildren.Id) grandchildren 
from Parents
left join Children on Parents.Id = Children.ParentId
left join Grandchildren on Children.Id = Grandchildren.ParentId
group by Parents.Id, Parents.[name]

see demo