[B]myTable1[/B]
[B](id) name[/B]
(1) Tom
(2) Jane
(3) Chris
(4) Judy
(5) Mary
(6) Jack
(7) James
(8) Bill
[B]myTable[/B]
[B](pom) kid[/B]
(1) 2
(3) 1
(3) 4
(5) 8
I have 2 tables like the above.
I like to say the tables says like the below.
Jane is a kid of Tom.
Tom and Judy are kids of Chris.
Bill is a kid of Mary.
Chris is the pom(papa or mom) of Tom and Judy.
Mary is the pom of Bill.
Tom is the pom of Jane.
I like to produce who are poms.
The code below produces the result below.
[B]code[/B]
select name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
group by pom
order by name
[B]result[/B]
Chris
Mary
Tom
Tom is the 2nd generation while Chris and Mary are the 1st generation,
because Tom is a kid of Chris.
I like to remove Tom for producing the 1st generation people only.
The following is my target result with myTable1 and myTable above.
[B]target result[/B]
Chris
Mary
How can I get my target result above?
The following is one of my trials, but it causes an error.
select name
from myTable1
left join myTable on myTable1.id=myTable.pom
[COLOR="#FF0000"]left join myTable on myTable1.id=myTable.kid[/COLOR]
where
myTable.pom is not null
and
[COLOR="#FF0000"]myTable.kid is null[/COLOR]
group by pom
order by name
