# Finding all step kids in recursive

``````[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?