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)

    NULL + condition

    Code:
    myTable5
    (id) mem
    (1)   1
    (1)   2
    (2)   1
    (2)   2
    (3)   1
    (3)   2
    (4)   1
    (4)   2
    (5)   1
    
    myTable5
    (id) mem
    (1)  1
    (1)  2
    (2)  1
    (4)  1
    (4)  2
    (5)  1
    I have data in myTable5 and myTable6 like the above.


    The code1 below added mem=1 in WHERE CLAUSE produces the result below
    Code:
    code1-1
    select id, mem from myTable5
    where mem=1
    
    result1-1
    (1) 1
    (2) 1
    (3) 1
    (4) 1
    (5) 1
    
    code1-2
    select id, mem from myTable6
    where mem=1
    
    result1-2
    (1) 1
    (2) 1
    (4) 1
    (5) 1
    The code2 below added mem=2 in WHERE CLAUSE produces the result below
    Code:
    code2-1
    select id, mem from myTable5
    where mem=2
    
    result2-1
    (1) 2
    (2) 2
    (3) 2
    (4) 2
    
    
    
    code2-2
    select id, mem from myTable6
    where mem=2
    
    result2-2
    (1) 2
    (4) 2
    
    I like to produce my target result below
    Code:
    when mem=1
    target result
    (3)
    
    
    when mem=2
    target result2
    (2) 
    (3)
    The followings are some of my trials for getting my target result.

    Code:
    trial code1
    select myTable5.id from myTable5
    left join myTable6 on myTable6.id=myTable5.id
    where myTable6.id is null and myTable5.mem=2
    trial result2
    (3)
    
    trial code2
    select myTable5.id from myTable5
    left join myTable6 on myTable6.id=myTable5.id
    where myTable5.mem=2 and myTable6.id is null
    
    trial result2
    (1)
    (4)
    
    target result
    (2)
    (3)

  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)
    Code:
    SELECT myTable5.id 
      FROM myTable5
    LEFT 
      JOIN myTable6 
        ON myTable6.id  = myTable5.id
       AND myTable6.mem = myTable5.mem  
     WHERE myTable5.mem = 2 -- or 1
       AND myTable6.id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    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)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT myTable5.id 
      FROM myTable5
    LEFT 
      JOIN myTable6 
        ON myTable6.id  = myTable5.id
       AND myTable6.mem = myTable5.mem  
     WHERE myTable5.mem = 2 -- or 1
       AND myTable6.id IS NULL
    It is very cool of you, thank you.


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
  •