Code:
myTable

(id) mom geo
(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.
Code:
sql code when variable.V is 1

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

result

(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.

Code:
(id) mom geo
(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.
Code:
sql code


select t1.id, t1.mom, t1.geo
  from myTable t1   
 
  join myTable t2
  on t1.mom = t2.id
 
  join myTable t3
  on t2.mom = t3.id
 
  join myTable t4
  on t3.mom = t4.id
 
  where  t1.mom = #variables.V# or
            t2.mom = #variables.V# or
            t3.mom = #variables.V# or
            t4.mom = #variables.V#
order by id
If I North America might have 100 generation kids, Should I add t100 like the below?

Code:
sql code


select t1.id, t1.mom, t1.geo
  from myTable t1   
 
  join myTable t2
  on t1.mom = t2.id
 
  omitted
 
  join myTable t99
  on t100.mom = t4.id
 
  where  t1.mom = #variables.V# or
            t2.mom = #variables.V# or
omitted
            t100.mom = #variables.V#
order by id
Is there any ways to reduce the the same pattern of making t1, t2 .... t99, t100 for producing all generation kids?