[b]myTable[/b]
[COLOR="Blue"](id) mom geo[/COLOR]
(0) 0 earth
(1) 0 North America
(2) 1 New York
(3) 0 Europe
(4) 1 L.A.
(5) 0 Asia
(6) 2 Manhattan
(7) 3 Paris
(8) 5 Seoul
I have data in myTable like the above.
North America has 2 generation kids, i.e. the 1st generation kid “New York” and his sister L.A, and the 2nd generation kid Manhattan.
The sql code below produces it like the result below.
[b]sql code when variable.V is 1[/b]
select t1.id, t1.mom, t1.geo
from myTable t1
join myTable t2
on t1.mom = t2.id
where t1.mom = #variables.V# or
t2.mom = #variables.V#
order by id
[b]result[/b]
(2) 1 New York
(4) 1 L.A.
(6) 2 Manhattan
Now let’s add 2 new records Manhattan’s kid “manhatan-1” and his grand-kid “manhatan-1-1” like the below.
[COLOR="Blue"](id) mom geo[/COLOR]
(9) 6 Manhatan-1
(10) 9 Manhattan-1-1
In order to produce all North America’s kids, I should add the red part below to the sql code above.
[b]sql code[/b]
select t1.id, t1.mom, t1.geo
from myTable t1
join myTable t2
on t1.mom = t2.id
[COLOR="Red"]join myTable t3
on t2.mom = t3.id
join myTable t4
on t3.mom = t4.id[/COLOR]
where t1.mom = #variables.V# or
t2.mom = #variables.V# or
[COLOR="Red"] t3.mom = #variables.V# or
t4.mom = #variables.V#[/COLOR]
order by id
If I North America might have 100 generation kids, Should I add t100 like the below?
[b]sql code[/b]
select t1.id, t1.mom, t1.geo
from myTable t1
join myTable t2
on t1.mom = t2.id
[COLOR="Red"][COLOR="Blue"]omitted[/COLOR]
join myTable t99
on t100.mom = t4.id[/COLOR]
where t1.mom = #variables.V# or
t2.mom = #variables.V# or
[COLOR="Red"][COLOR="blue"]omitted[/COLOR]
t100.mom = #variables.V#[/COLOR]
order by id
Is there any ways to reduce the the same pattern of making t1, t2 … t99, t100 for producing all generation kids?