An SQL Teaser Question
I would like to generate an Org Chart using SQL. I am able to generate a
list when working from the root.
However, I have hit a mental block when it comes to traversing the tree from
a branch to leaf.
For example, starting from "Corporate HQ" is easy. Simply start where
"parent is null".
How do you traverse the org chart when starting at "Div - 1"? Do I need
some sort of "levels" indicator?
Any thoughts would be appreaciated.
Steve Romankiw
-----------------------------------------------------
-- Source below
--
-- Sample Org chart --
Corporate HQ
|- Div 1
|- Div 1a
|- Div 1b
|- Div 1ba
|- Div 1baa
|- Div 1bab
|- Div 1bac
|- Div 1c
|- Div 2
|- Div 2a
---
-- Sample Query to get all org chart from top-level
---
select B1.name as parent, A1.name as next_level
from org_chart A, org_chart B, company A1, company B1
where A.parent = B.child
and A.child = A1.id
and B.child = B1.id;-- and B1.name = 'Div - 1';
---
-- SQL Create tables and populate with sample data
---
drop table org_chart;
create table org_chart (
parent integer,
child integer );
drop table company;
create table company (
id integer,
name varchar(25) );
insert into company values (1,'Corporate HQ');
insert into company values (2,'Div - 1');
insert into company values (3,'Div - 2');
insert into company values (4,'Div - 1a');
insert into company values (5,'Div - 1b');
insert into company values (6,'Div - 1c');
insert into company values (7,'Div - 2a');
insert into company values (8,'Div - 1ba');
insert into company values (9,'Div - 1baa');
insert into company values (10,'Div - 1bab');
insert into company values (11,'Div - 1bac');--
insert into org_chart values (0,1);
insert into org_chart values (1,2);
insert into org_chart values (1,3);
insert into org_chart values (2,4);
insert into org_chart values (2,5);
insert into org_chart values (2,6);
insert into org_chart values (3,7);
insert into org_chart values (5,8);
insert into org_chart values (8,9);
insert into org_chart values (8,10);
insert into org_chart values (8,11);
commit;