Thread: When exists

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    3
    Rep Power
    0

    When exists


    I have the following data:

    A B
    1 1
    2 2
    2 .
    3 .

    I want a third row that gives the value 1 when A does not exists in the whole column B:

    A B output
    1 1 0
    2 2 0
    2 . 0 (because the value of ID1 does exist in ID2)
    3 . 1 (because the value of ID1 doesn't exist in ID2)
    Last edited by DaanOoms; October 31st, 2012 at 11:00 AM. Reason: Unclear representation
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT x.*
         , y.b IS NULL output 
      FROM my_table x 
      LEFT 
      JOIN my_table y 
        ON y.b = x.a;

IMN logo majestic logo threadwatch logo seochat tools logo