Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2010
    Posts
    5

    Unanswered: Selecting records when a matching record not in another file

    I am trying to find all records from SGF where they don't have a corresponding record in SPG with an AP_Type of blank. If they have no record in SPG, I DO want them.

    Any thoughts on how I can rewrite this?


    FROM tableA SGF
    LEFT OUTER JOIN tableB SPG ON SGF.NUM = SPG.NUM,

    WHERE
    SPG.AP_TYPE = ' '

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I have always liked NOT EXISTS.
    Dave

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i have always liked LEFT OUTER JOIN with IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by r937 View Post
    i have always liked LEFT OUTER JOIN with IS NULL
    I believe this only works on non-duplicated data, I remember there is some catch where this will fail.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Gagnon View Post
    I believe this only works on non-duplicated data, I remember there is some catch where this will fail.


    ummm...in a word....

    No
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by Brett Kaiser View Post
    ummm...in a word....

    No
    there is a catch I know I ran into it, perhaps when joining on a column that is nullable?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    you shouldn't use nullable column in a test for existence.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by rdjabarov View Post
    you shouldn't use nullable column in a test for existence.
    take for example STATE_ABBR (think address, Country->State->City), International addresses would have this as NULL, sure you should have a lookup table but for this example since it is only 2 characters, it is placed in-table.

    FWIW I always use NOT EXISTS (SELECT 1 FROM ...)

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    you shouldn't use nullable columns for existence testing.

    And, FWIW, ...exists (select 1 from ...) and exists (select * from ...) have no difference in performance, if you check for existence in 1 table only.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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