Count how many children the father has (Self Related)

Question

Table:

FilhoID INT PK,
PaiID INT FK

Query:

SELECT FilhoID, PaiID, (SELECT COUNT(PaiID) FROM tabela WHERE PaiID = FilhoID) as 'Total Filhos' FROM tabela

My doubt:

My SELECT within the SELECT, I wanted him to count how many children the father has. I will then check with that number. (I may have reversed the father-son issue, but I believe it doesn’t stop you from helping me)

In the column Total Filhos, I wish you’d show up like this

FilhoID    PaiID     Total Filhos
   1       null           3
   2        1             1
   3        1             0
   4        1             0

That is to:

• Filhoid 1 -> 3 Paiid;

• Filhoid 2 -> 1 Paiid;

• Filhoid 3 -> 0 Paiid;

• Filhoid 4 -> 0 Paiid.

In C#, I send it to a List

Is that clear? They need some more detail?

Answer 1

The important thing is to be able to define correctly the set of Ids (Children). Because there may be Paiid that are not in a relation Paiid - Paiid. For this you can start by making a union of the two tables in order to get all the Ids:

• Ids of children extracted from relationships Filhoid - Paiid
• Ids of parents who have no father

Then you just need to count the number of children for each Paiid, for example with a query directly in the most external SELECT.

SELECT IDs.FilhoID, 
       IDs.PaiID, 
       (SELECT COUNT(DISTINCT FilhoID) from tabela t2 where t2.PaiID = IDs.FilhoID) AS TotalFilhos
  FROM
  (
    SELECT FilhoID, PaiID
      FROM tabela
     UNION
    SELECT t1.PaiID, NULL
      FROM tabela t1
      LEFT JOIN tabela t2
        ON t2.FilhoID = t1.PaiID
     WHERE t2.FilhoID IS NULL
  ) IDs
 ORDER BY 1

For a table with the following content:

PaiID   FilhoID
1       2
1       3
1       4
2       5

This will be the result:

FilhoID     PaiID   TotalFilhos
1           NULL    3
2           1       1
3           1       0
4           1       0
5           2       0