SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    finding records which is not read by Tom the receiver.

    Code:
    myTable7
    (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]
    Code:
    myTable8
    (id) mother
    (3)  1     / Both (3) and 1 in myTable7 are written by Tom.  
    (4)  2     / Both (4) and 2 in myTable7 are written by Mary.
    (5)  1     / (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
    (6)  1     / Both (6) and 1 in myTable7 are written by Tom.
    Code:
    myTable9
    (id) name
    (2) [Tom]    / (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.
    (4) [Tom]    / (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.
    (5) [Tom]    / (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.
    Code:
    target result
    (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.
    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    ^ I'm with him
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •