hierarchy

[mehrdadc48]

Hi, How can I find all leaves (nodes that don't have any child), in a hierarchy structure in my table in sql?

Best wishes

[J4amieC]

Peronally, when I represent Heirachies in a relational table I always add a boolean "IsLeaf" field so I can query just this situaltion easily (SELECT * FROM MyHeirachy WHERE IsLeaf=1) However, it should be easy enough without it, just look for any rows where there is no row referencing this row as its parent.

SELECT *
FROM MyHeirachy H
WHERE NOT EXISTS (SELECT * FROM MyHeirachy WHERE ParentID=H.ID)

[i j russell]

The following code uses the Employees table from Northwind. WITH EmployeeStructure AS ( SELECT employeeid, reportsto, 0 as level FROM Employees WHERE reportsto is null UNION ALL SELECT e.employeeid, e.reportsto, level + 1 FROM Employees e INNER JOIN EmployeeStructure es ON es.employeeid = e.reportsto ) SELECT * FROM EmployeeStructure WHERE EmployeeID NOT IN (SELECT reportsto FROM EmployeeStructure GROUP BY reportsto HAVING ReportsTo IS NOT NULL) ORDER BY level ASC