SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

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

    records which are not a variable

    Code:
    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.

    Code:
    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.
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •