A nice little ‘quickie’ to flatten things like department / employee tables, which often employ an ID/ParentID relationship. At times I’ve needed to get a department (say ID 100) out of the table, but also all the children of that department.
This can be achieved using table extensions ala:
with DepartmentHierarchy (departmentId, parentId)
as
(
select
departmentId, parentId
from
department d
where
departmentId = /*Top level parent id*/
union all
select
d.departmentId, d.parentId
from
department d
inner join departmenthierarchy dh on d.parentId = dh.departmentID
)
select
departmentId, parentId
from
departmenthierarchy
Replacing my comment with your top level departmentid will return you that department, but also all of its child records. These sorts of table extensions can be useful particularly in security models where you want to find out which departments etc your users have access to view data for without having to resort to cursors.
Recent Comments