Calling direct ancesters in recursive

[b]data in myTable[/b]

[COLOR="Blue"](id) mom geo[/COLOR]

(0) 0 earth
(1) 0 North America
[COLOR="Red"](2)[/COLOR] 1 America
(3) 0 Europe
(4) 2 L.A.
(5) 2 New York
(6) 5 Manhatan
(7) 3 France
(8) 7 Paris

I have data in myTable like the above.

When myVariable is COLOR=“Red”[/COLOR] ,
the query below produces all kids of the record COLOR=“red”[/COLOR].

[b]query[/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]

(4) 2 L.A.
(5) 2 New York
(6) 5 Manhatan

Instead of the kids, I like to produce ancesters of the recordCOLOR=“Red”[/COLOR] and the recordCOLOR=“red”[/COLOR] itself.

The following trial code seems not to work as I want.

[b]trial code[/b]

select t1.id, t1.mom, t1.geo
  from myTable t1   
 
  join myTable t2
  on t1.id = t2.mom 
 
  where  t1.id = #variables.V# or
            t2.id = #variables.V# 
        order by id

[b]result of the trial code[/b]

(1) 0 North America
(2) 1 America
(2) 1 America

[b] my target result[/b]

(0) 0 earth
(1) 0 North America
(2) 1 America
  1. You aren’t getting earth, because earth is not a first grade ancestor, but second (so you’d have to add another join to the query
  2. You’re getting America twice, because there are two rows in t1 with mom = 2. Use SELECT DISTINCT to get rid of the duplicates

Thank you, guido, for the hint. I got the result what I want now.