Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    5

    Unanswered: Selecting Unmatched Records Based On Multiple Fields

    I need to list all the records in Table2 which don't have matching field values in Table1.

    This the the exact opposite of what I need:
    SELECT DISTINCT
    Field1,
    Field2,
    Field3,
    Field4,
    Field5
    FROM
    [Table1]
    WHERE EXISTS(
    SELECT DISTINCT
    FieldA,
    FieldB,
    FieldC,
    FieldD,
    FieldE
    FROM
    [Table2]
    )

    The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.

    What I need, however, is the exact opposite.

    I tried the above using NOT EXISTS but I get no records at all.

    How do do this?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I would have approached it differently:
    Code:
    SELECT DISTINCT
      Field1,
      Field2,
      Field3,
      Field4,
      Field5
    FROM
      [Table1] A RIGHT OUTER JOIN [TABLE2] B ON
         A.Field1 = B.FieldA 
         AND A.Field2 = B.FieldB 
         AND A.Field3 = B.FieldC 
         AND A.Field4 = B.FieldD 
         AND A.Field5 = B.FieldE 
    WHERE
      A.Field1 is NULL



    Regards,

    hmscott


    Quote Originally Posted by Clif001
    I need to list all the records in Table2 which don't have matching field values in Table1.

    This the the exact opposite of what I need:
    SELECT DISTINCT
    Field1,
    Field2,
    Field3,
    Field4,
    Field5
    FROM
    [Table1]
    WHERE EXISTS(
    SELECT DISTINCT
    FieldA,
    FieldB,
    FieldC,
    FieldD,
    FieldE
    FROM
    [Table2]
    )

    The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.

    What I need, however, is the exact opposite.

    I tried the above using NOT EXISTS but I get no records at all.

    How do do this?
    Have you hugged your backup today?

  3. #3
    Join Date
    Jul 2004
    Posts
    4

    check it out with NOT IN

    is it possible to try this out with the NOT IN clause ?
    If you have a relation between the tables

    select * from table2 where 'foreign key' NOT IN (select 'primary key' from table1 )

  4. #4
    Join Date
    Jul 2004
    Posts
    5
    I'm sorry but that doesn't seem to work. I get one record which is all nulls and I do not get the record I know exists in Table2 but not in Table1.

    Amazingly I don't have any records in either table where Field1 (or FieldA) is null so I really shouldn't have gotten the record I did. (However doing a Count(A.Field1) gives me 0.

  5. #5
    Join Date
    Jul 2004
    Posts
    5
    Quote Originally Posted by praveenvc
    is it possible to try this out with the NOT IN clause ?
    If you have a relation between the tables

    select * from table2 where 'foreign key' NOT IN (select 'primary key' from table1 )
    Please excuse me. I'm not a DBA, I'm just a VB Programmer so I'm not quite sure what you are saying.

    Neither table is indexed. Can I do this without modifying either table?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This gets rather quirky if any of the columns allow NULL values, but assuming that they are all non-NULL you could use:
    Code:
    SELECT DISTINCT
       Field1, Field2, Field3, Field4, Field5
       FROM [Table1]
       WHERE NOT EXISTS(SELECT *
          FROM [Table2]
          WHERE  [Table2].FieldA = [Table1].Field1
             AND [Table2].FieldB = [Table1].Field2
             AND [Table2].FieldC = [Table1].Field3
             AND [Table2].FieldD = [Table1].Field4
             AND [Table2].FieldE = [Table1].Field5)
    -PatP

  7. #7
    Join Date
    Jul 2004
    Posts
    5
    My co-worker decided on another tact.

    Thanks for the help, though.

Posting Permissions

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