Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Posts
    139

    Unanswered: Query: How to exclude doubles from 2 tables?

    Consider the following tables:

    Table 1:
    Company Date Value
    ABC 01-jan-2001 50 *
    ABC 01-jan-2002 10 *
    ABC 01-jan-2004 15

    Table 2:
    Company Date Value
    ABC 01-jan-1999 25
    ABC 01-jan-2000 37
    ABC 01-jan-2001 75 *
    ABC 01-jan-2002 40 *
    DEF 01-jan-2001 35
    DEF 01-jan-2002 40
    DEF 01-jan-2003 34

    What I need is a selection of table 2, that excludes those records where the combination of company and date also exists in table1 (marked *):
    Required Subset of Table 2:
    Company Date Value
    ABC 01-jan-1999 25
    ABC 01-jan-2000 37
    DEF 01-jan-2001 35
    DEF 01-jan-2002 40
    DEF 01-jan-2003 34

    Who knows the query syntax to achieve this selection?
    Any help highly appreciated!

  2. #2
    Join Date
    Jan 2003
    Location
    Midwest
    Posts
    138
    SELECT * FROM table2 WHERE COMPANY NOT IN (SELECT COMPANY FROM table1) AND [Date] NOT IN (SELECT [Date] FROM table1)

  3. #3
    Join Date
    Apr 2002
    Posts
    139
    Hi Mennoch,

    Thanks for your input.
    After playing around for a while I ended up with something like:

    SELECT * FROM Table2 WHERE ([Company] & [Date]) Not In (SELECT ([Company] & [Date]) FROM Table1;

    I learned that the combination of Not & And gives unexpected outcomes, reminds me somewhat of the days that I was melting down transistors on a piece of circuit board, trying to make my own NAND & NOR switches :-).
    Anyway, it works fine now!

Posting Permissions

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