1st generation people only

[dotJoon]

myTable1
(id) name
(1) Tom
(2) Jane
(3) Chris
(4) Judy
(5) Mary
(6) Jack
(7) James
(8) Bill

myTable
(pom) kid
(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.

code
select name
from myTable1
left join myTable on myTable1.id=myTable.pom
where
myTable.pom is not null
group by pom
order by name

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

target result

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
left join myTable on myTable1.id=myTable.kid
where
myTable.pom is not null
and
myTable.kid is null
group by pom
order by name

[guido2004]

You're almost there, but since you're linking the same table twice, you must give them aliasses, otherwise MySQL can't know when you're using what "instance" of the table.

select DISTINCT name
from myTable1
left join myTable AS a
on myTable1.id = a.pom
left join myTable AS b
on myTable1.id = b.kid
where a.pom is not null
and   b.kid is null
order by name

[r937]

joon obviously forgot all the many, many threads where i already taught him this stuff

all i can add to this thread is my best wishes to guido and i hope you have a lot of patience, buddy

by the way, a.myTable.pom and b.myTable.kid are malformed

[guido2004]

by the way, a.myTable.pom and b.myTable.kid are malformed

Forgot to delete the myTable part
I've corrected the query in my previous post.

[dotJoon]

by the way, a.myTable.pom and b.myTable.kid are malformed

Do you mean myTable which has 2 columns "pom and kid" is badly designed?

[dotJoon]

As I see carefully the posts, I found it means a kind of typo instead of telling a badly designed table.
Thank you, guido2004 and rudy.
I hope you enjoy your patience like I do.