Nov 052014
 

 

Many times it is required to fetch data in a hierarchical form. The result can be fetched using Recursion. Recursion means self-reference.

To explain it in a better way look at the following example:

Step 1: Create “Category” table with 3 columns, ID as Primary key, Name and ParentID that references ID column of same column.

CREATE TABLE CATEGORY

(

  ID        NUMBER Primary Key,

  NAME      VARCHAR2(200 BYTE),

  PARENTID  NUMBER References category(ID)

);

Step 2: Insert few records into “Category” table as below:

insert into category values (1,’Test’,0);

insert into category values (2,’Test1′,1);

insert into category values (3,’Test2′,1);

insert into category values (4,’test11′,2);

insert into category values (5,’test114′,4);

commit;

Step 3: Select the “Category” table to view inserted records.

select * from category;

ID NAME PARENTID
1 Test 0
2 Test1 1
3 Test2 1
4 test11 2
5 test114 4

Step 4: Write a SQL Query to show name in a hierarchical form starting from root value.

WITH EntityChildren (id,Name, ParentID) AS

(

SELECT id,Name, ParentID  FROM category WHERE ID = 1

UNION ALL

SELECT e.id, e2.Name ||’–>’ || e.Name, e.ParentID FROM category e , EntityChildren e2 where e.ParentID = e2.id

)

SELECT * from EntityChildren;

 

(OR)Using INNER JOIN Clause

WITH EntityChildren (id,Name, ParentID) AS

(

SELECT id,Name,ParentID  FROM category WHERE ID = 1

UNION ALL

SELECT e.id, e2.Name ||’–>’ || e.Name, e.ParentID FROM category e INNER JOIN EntityChildren e2 on e.ParentID = e2.id

)

SELECT * from EntityChildren;

The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being re-queried by each reference.

Output:

ID NAME PARENTID
1 Test 0
2 Test–>Test1 1
3 Test–>Test2 1
4 Test–>Test1–>test11 2
5 Test–>Test1–>test11–>test114 4

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)