with CTE( lvl,par,child) as
(select 1, par,child from TableA where child = 'D'
union all
select c.lvl + 1, a2.par, a2.child from TableA a2, CTE c
where a2.child = c.par
and c.lvl < 99 )
select * from CTE
lvl is to make sure you don't loop endlessly when there would exist a row "A is child of A" or any other circular reference (a=>b, b=> a).
1) It is using Common Table Expression : instead of using a subselect, you can now define a kind of temp table just before your select :
Code:
with temptablename(column1, column2) as (any select)
which you can then use in your select
Code:
select * from temptablename join etc....
2) Using this technique you can code "recursive sql"
you UNION ALL a table with itself :
- in the first leg of the union all you write a starting select :
Code:
select 1, par,child from TableA where child = 'D'
this would give the row 'D=>C'
-in the second leg you use the result of that starting select (which is now stored in temptablename , I used CTE ) and you join with something else :
Code:
select c.lvl + 1, a2.par, a2.child from TableA a2, CTE c
where a2.child = c.par
and c.lvl < 99
thus for the row (1,c,d) in CTE, you join with you tableA finding C=>B which gets stored in CTE (2,b,c)
And for this new row(s) of cte you repeat step 2, ... ad infinitum
In this example each repetition/recursion only adds one new row.
But suppose you go from parent to child.
then each repition adds several rows and the result set will grow exponentialy.
when using recursive SQL you better always built in a failsafe : a number/level which starts at 1 and adds 1 for each repetition , always write a where lvl < something in the where clause of the second leg of the union all.
hope this helps.
There are several examples of recursive sql and a several better explanations on the net , just google some.
