[B]myTable1
(id) name[/B]
(1) Tom
(2) Jane
(3) Chris
(4) Judy
(5) Mary
(6) Jack
(7) James
(8) Bill
[B]myTable
(pom) kid[/B]
(1) 2
(3) 1
(3) 4
(5) 8
I have 2 tables like the above.
The code1 below produces the result1 below.
[B]code1[/B]
select id, name
from myTable1
left join myTable as myPom on myTable1.id=myPom.pom
left join myTable as myKid on myTable1.id = myKid.kid
where
myKid.pom = 3 /* when the variable is 3
order by id
[B]result1[/B]
(1) Tom
(4) Judy
I like to retrieve all persons in myTable1 except Tom and Judy who are not kids of Chris.
The code2 below is one of my trials for it, however it failed in producing my target result below.
[b]code2[/b]
select id, name
from myTable1
left join myTable as myPom on myTable1.id=myPom.pom
left join myTable as myKid on myTable1.id = myKid.kid
where
myKid.pom <> 3
order by id
[b]result2[/b]
(2) Jane
(8) Bill
[b]target result[/b]
(2) Jane
(3) Chris
(5) Mary
(6) Jack
(7) James
(8) Bill