I’ve come across a very neat trick in Oracle that I thought I’d share. Its useful for situations where you can have circular references in the data diagram, for example when the table includes a column which is a reference for another entry in the same table. Usually it would be necessary to write some function which could be called recursively and allow you to traverse this tree layout, however in Oracle there is the CONNECT BY PRIOR syntax.
The users and groups example
Sticking with my earlier example let’s say I’ve got an employees table and the employee can have a manager, which is an employee
|5||miranda||Miranda||De Silva||4||Chief Assistant to the Assistant Chief|
select first_name || ' ' || last_name, level from users connect by prior id = manager start with id = 1
Will output the list of people followed by their reports. We can distinguish who is where in the tree using the magical column “level” which is available when we connect in this way. I’ve used the level to indent the name column by two spaces for each level down the tree the person is and to show their job title:
select lpad(' ', (level - 1) * 14) || first_name || ' ' || last_name, job_title from users connect by prior id = manager start with id = 1