Posts

Showing posts from December, 2013

Get Immediate and Ultimate Parents with levels

WITH r (id, parent, ultimate_parent, name, ultimate_parent_name, lvl)as(SELECT id, parent_id AS parent, parent_id AS ultimate_parent, name, name as ultimate_parent_name,0 lvl FROM mytable WHERE parent_id = id -- identifies a rootUNIONALLSELECT m.id, r.id, ultimate_parent, m.name, r.ultimate_parent_name, r.lvl +1FROM r join mytable m on m.parent_id = r.id -- joins child with parentWHERE m.parent_id <> m.id -- to avoid cycles)SELECT*FROM r ;The first part of the subquery fetches the roots and the second part connects the children. Parent is the immediate parent andultimate_parent, the ultimate parent.