records which are not a variable

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

The code1 below produces the result1 below.

code1
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

result1
(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.

code2
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

result2
(2) Jane
(8) Bill

target result
(2)  Jane
(3)  Chris
(5)  Mary
(6)  Jack
(7)  James
(8)  Bill

[r937]

come on, joon, i've taught you all this stuff before

when you want to find rows in one table where matching rows in another table don't exist, you use a LEFT OUTER JOIN with an IS NULL check

SELECT myTable1.id
     , myTable1.name
  FROM myTable1
LEFT OUTER
  JOIN myTable as myPom 
    ON myPom.pom = myTable1.id
LEFT OUTER
  JOIN myTable as myKid 
    ON myKid.kid = myTable1.id
   AND myKid.pom = 3
 WHERE myKid.kid IS NULL 
ORDER 
    BY myTable1.id

notice it's an AND condition in the ON clause