Post reply

Rows in Table1 that don''t exist in Table 2 when it''s a compound key

  • March 21, 2006 at 9:08 pm

    #171706

    This ALWAYS comes up, but there's got to be a better way then how I'm doing it..

    Table1 and Table2 both have FName and LName columns.  If I want to get all of the Table1 rows that don't have a corresponding match in Table2 I build a NOT IN on a concantenated string like this:

    SELECT * FROM Table1 WHERE (FNAME+'.'+LNAME) NOT IN(SELECT FNAME+'.'+LNAME FROM Table2)

    Is there a more elegant way to do this with Joins in a single statement? I constantly need to analyze tables for exclusion sets on compound keys; I know this has got to be a common problem but I can't figure out the syntax for the life of me.

    Thanks alot

    Bob Allen (ballen@ChainbridgeTech.com)

  • March 21, 2006 at 10:14 pm

    #628015

    Is there a Foreign Key in Table2? If there is try,

    SELECT A.*

    FROM Table1 A INNER JOIN Table2 B ON A.PrimaryKeyID = B.ForeignKeyID

    Kindest Regards,

  • March 21, 2006 at 10:46 pm

    #628017

    Option 1 - use NOT EXISTS

    SELECT * FROM Table1 As t1

    WHERE NOT EXISTS

    (

      SELECT * FROM Table2 As t2

      WHERE t2.FNAME = t1.FNAME

      AND     t2.LNAME = t1.LNAME

    )

    Option 2 - use LEFT JOIN and check NULL

    SELECT t1.*

    FROM Table1 As t1

    LEFT JOIN Table2 As t2

      ON   t2.FNAME = t1.FNAME AND

             t2.LNAME = t1.LNAME

    WHERE t2.FNAME IS NULL

     

  • March 22, 2006 at 7:55 am

    #628101

    Thanks man;

    The NOT EXISTS does not work, but option 2 (LEFT JOIN with a check NULL) does.  The only issue, which is minior, is to make sure that whaterver column I use in my "IS NULL" check doesn't contain any NULLS, but that's trivial because a NULL:NULL wouldn't show up in the join anyway.  Pretty cool.  Doing the "...ON(T1.Col1<>t2.Col1 AND T1.Col2<>T2.Col2)"  really seemed like it should give me the rows in T1 that don't have a corresponding row in T2, but no dice.

     

    Thanks again.

  • March 22, 2006 at 8:39 am

    #628117

    I never realized that the WHERE operated after the JOIN....aaargh!  I've been doing those hideous concantenation NOT IN operations for years...

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply