[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