The syntax for query hierarchical data is-
SELECT columns
FROM table_name
WHERE where_condition
CONNECT BY PRIOR condition_for_next_level
START WITH start_condition
ORDER SIBLINGS BY columns
Let's try it with a practical example.
CREATE
TABLE location(
id NUMBER,
name
VARCHAR2(100),
parent NUMBER
);
INSERT
INTO location(id,name,parent) VALUES(1,'Earth',0);
INSERT
INTO location(id,name,parent) VALUES(2,'Asia',1);
INSERT
INTO location(id,name,parent) VALUES(3,'Europe',1);
INSERT
INTO location(id,name,parent) VALUES(4,'America',1);
INSERT
INTO location(id,name,parent) VALUES(5,'Africa',1);
INSERT
INTO location(id,name,parent) VALUES(6,'Oceania',1);
INSERT
INTO location(id,name,parent) VALUES(7,'Bangladesh',2);
INSERT
INTO location(id,name,parent) VALUES(8,'India',2);
INSERT
INTO location(id,name,parent) VALUES(9,'Pakistan',2);
INSERT
INTO location(id,name,parent) VALUES(10,'Singapore',2);
INSERT
INTO location(id,name,parent) VALUES(11,'Malaysia',2);
INSERT
INTO location(id,name,parent) VALUES(12,'England',3);
INSERT
INTO location(id,name,parent) VALUES(13,'France',3);
INSERT
INTO location(id,name,parent) VALUES(14,'Turkey',3);
INSERT
INTO location(id,name,parent) VALUES(15,'Scotland',3);
INSERT
INTO location(id,name,parent) VALUES(16,'Mexico',4);
INSERT
INTO location(id,name,parent) VALUES(17,'Canada',4);
INSERT
INTO location(id,name,parent) VALUES(18,'USA',4);
INSERT
INTO location(id,name,parent) VALUES(19,'Dhaka',7);
INSERT
INTO location(id,name,parent) VALUES(20,'Rajshahi',7);
INSERT
INTO location(id,name,parent) VALUES(21,'Chittagong',7);
INSERT
INTO location(id,name,parent) VALUES(22,'Las Vegas',18);
INSERT
INTO location(id,name,parent) VALUES(23,'New Orlince',18);
INSERT
INTO location(id,name,parent) VALUES(24,'New York',18);
Now
SELECT * FROM location;
Will give..
ID NAME PARENT
-----------------------------------------------
1 Earth 0
2 Asia 1
3 Europe 1
4 America 1
5 Africa 1
6 Oceania 1
7 Bangladesh 2
8 India 2
9 Pakistan 2
10 Singapore 2
11 Malaysia 2
12 England 3
13 France 3
14 Turkey 3
15 Scotland 3
16 Mexico 4
17 Canada 4
18 USA 4
19 Dhaka 7
20 Rajshahi 7
21 Chittagong 7
22 Las Vegas 18
23 New Orlince 18
24 New York 18
Now
SELECT
lpad(' ',8*(level-1))||NAME AS name
FROM LOCATION
CONNECT
BY
PRIOR id = parent
START
WITH parent = 0
Will give the following result
NAME
------------
Earth
Asia
Bangladesh
Dhaka
Rajshahi
Chittagong
India
Pakistan
Singapore
Malaysia
Europe
England
France
Turkey
Scotland
America
Mexico
Canada
USA
Las Vegas
New Orlince
New York
Africa
Oceania
You can also use ORDER SIBLINGS BY to sort siblings
Posts
