How to select lowest level in hierarchy from table

Question

I have a table with parent/child relationship:

Table A
Column Id int
Column Parent_Id int
Column Description text

An example set of data would be:

999, NULL, 'Corp.'
998, 999, 'Div1',
997, 999, 'Div2', 
510, 998, 'Child Div1A',
110, 997, 'Child Div2A',
120, 997, 'Child Div2B',

My query needs to return the lowest set of children, given a parent. So, for example, if given 999, I would return 510, 110, 120. But given 997, I would return only 110 and 120. If given 110, nothing would return. I can't figure out how to format my query quite correctly. I started by JOIN-ing the table on itself, but it seems like that would only get me 1 level down when I actually neeed to go N-levels down.

Answer 1

Declare @t Table(ID int,Parent_ID int,Description varchar(20))

insert into @t values(999, NULL, 'Corp.')
insert into @t values(998, 999, 'Div1')
insert into @t values(997, 999, 'Div2')
insert into @t values(510, 998, 'Child Div1A')
insert into @t values(110, 997, 'Child Div2A')
insert into @t values(120, 997, 'Child Div2B')

;WITH Rollups AS (
    SELECT Id, Parent_Id, Description 
    FROM @t WHERE ID = 999
    UNION ALL
    SELECT parent.Id, parent.Parent_Id, child.Description
    FROM @t parent 
    INNER JOIN Rollups child ON child.Id = parent.Parent_Id
)
SELECT *
FROM Rollups
Where not Exists(Select * from @t where Parent_Id=Rollups.ID)