Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Unanswered: comparing tables

    I have two tables and I need to know which records are in one but not in the other. The problem is the reference id is broken into 3 sections and I need to know which complete id's are not in the table.
    so there are 3 fields that represent an individual ID e.g
    Field 1: AN
    Field 2: 03
    Field 3: 121A

    It is easy to find which are in both but not which are missing. All ideas welcome.
    Thanks.

  2. #2
    Join Date
    Mar 2003
    Location
    Romania
    Posts
    32
    Here is Table1:
    Field1 Field2 Field3
    1 2 3
    1 2 4

    and Table2
    Field1 Field2 Field3
    1 2 3
    1 1 1

    and here is how to find records in Table1 that are not present in Table2:

    SELECT Table1.*
    FROM Table1 LEFT JOIN Table2 ON (Table1.Field3 = Table2.Field3) AND (Table1.Field2 = Table2.Field2) AND (Table1.Field1 = Table2.Field1)
    WHERE (((Table2.Field1) Is Null) AND ((Table2.Field2) Is Null) AND ((Table2.Field3) Is Null));

    The result is:
    Field1 Field2 Field3
    1 2 4

    I hope this is what you want.
    Florin Profeanu
    floreanuprofin@yahoo.com

  3. #3
    Join Date
    Apr 2003
    Posts
    2
    thanks very much that works great.
    Caz

Posting Permissions

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