Search code examples
sqlsql-serverhierarchical-data

SQL hierarchical data

I have the following requirement in sql server where I need to report data of children of catg "FIN" which are direct descendants of a parent.

Input

parent,child,parent_catg,child_catg
A   A   FIN FIN
A   B   FIN FIN
B   B   FIN FIN
A   C   FIN FIN
B   C   FIN FIN
C   C   FIN FIN
B   D   FIN FIN
C   D   FIN FIN
E   E   OTH OTH
E   F   OTH OTH
A   G   FIN FIN
B   G   FIN FIN
A   H   FIN FIN
P   P   FIN FIN
P   Q   FIN FIN

Output

Eligible children for reporting
B
H
Q

Can anyone please help?

Solution

  • You can use group by and count(). ie:

     select child 
 from myTable
 where child_catg = 'FIN' and parent <> child
 group by child
 having count(*) = 1;