Finding records which is not read by Tom the receiver

Databases
1 
[b]myTable7[/b]
(id) message                           name
(1) Tom's message1                     [Tom]
(2) Mary's message2 which send to Tom  [Mary]
(3) Tom's message3                     [Tom]
(4) Mary's message4 which send to Tom  [Mary]
(5) Jack's message5 which send to Tom, and Tom already read it  [Jack]
(6) Tom's message6                     [Tom]
(7) Jane's message7                    [Jane]

[B]myTable8[/B]
(id) mother
[COLOR="gray"](3)  1     / Both (3) and 1 in myTable7 are written by Tom.  
(4)  2     / Both (4) and 2 in myTable7 are written by Mary.[/COLOR]
[COLOR="blue"](5)  1[/COLOR]     [COLOR="Blue"]/ (5) in myTable7 is written by Jack and 1 in myTable7 is written by Tom.
              This means Tom already read it because the person who wrote 
              mother's  message can read his or her kids' message[/COLOR]
[COLOR="gray"](6)  1     / Both (6) and 1 in myTable7 are written by Tom.[/COLOR]

[B]myTable9[/B]
(id) name
[COLOR="red"](2) [Tom][/COLOR]    / (2) in myTable7 is written by Mary and sended to Tom.
               Tom didn't read it because there is no record about it in myTable8.
[COLOR="red"](4) [Tom][/COLOR]    / (4) in myTable7 is written by Jane and sended to Tom.
               Tom didn't read it because there is no record about it in myTable8.
[COLOR="blue"](5) [Tom][/COLOR]    / (5) in myTable7 is written by Jack and sended to Tom.
               Tom did read it because there is a record about it in myTable8.

I have 3 tables like the above.

I like to find every message which is not read by Tom although it is sended to Tom.
The following is my target result.

[b]target result[/b]
(2) Mary's message2 which send to Tom  [Mary]
(4) Mary's message4 which send to Tom  [Mary]

Both of my target record are incidentally written by Mary.
Mary is not the factor of finding my target result because that is incidental of this case.
But Tom is the factor of finding my target result.

The following is one of my trials.

select myTable7.id,message,myTable7.name
from myTable8
left join myTable7 on myTable8.id=myTable7.id
left join myTable9 on myTable8.id=myTable9.id
where myTable8.id is null and myTable9.name="Tom"
2

we’ve done at least a hunnert queries together, you and i, that have the exactly the necessary solution, joon

geez, i wish you would spend as much time actually learning this stuff as you do constructing your plentiful pleas for help

i’m going to ask you to please search the forum, and i am sure you will find one of your previous threads that has the answer

hint: your condition on table9 is in the wrong place

3

^ I’m with him :nono: