Results 1 to 4 of 4

Thread: Exceptions table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Exceptions table

    Hello,

    How can I return records from table-1 that do not match with records in table-2?

    I have a master table (say Table-1) that contains all the data. Another table (Table-2) contains records that I do not want to see in the resulting table.

    I need to do following:
    (Table-1) - (Table-2) = Query result set.


    There is a report that I have created; as the project proceeded I was asked to enter some exceptions (records that we do not want to see in the result set). So I hard coded those exceptions in my SQL query. I am thinking of creating an exceptions table to which I can add or remove exceptions without having to edit the code.

    Could anyone please guide me to which approach should I follow.

    Regards,
    Rajesh

  2. #2
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    216
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exceptions table

    What fields are you needing to compare? This can be done in one of a few ways. Here is one possibility:

    Code:
     
    SELECT T1.*
    FROM Table1 T1
    LEFT JOIN Table2 T2
    ON 
    T1.Field1 = T2.Field1
    AND
    T1.Field2 = T2.Field2
    AND 
    (etc. for all the fields you need to compare)
    WHERE T2.Field1 IS NULL
    If nulls are possible in any of the fields, you will want to wrap each comparison in Nz(), for example this will work for text fields:

    Code:
     
    Nz(T1.Field1,'') = Nz(T2.Field1,'')

  3. #3
    Board Regular
    Join Date
    Jun 2007
    Location
    Surrey, Canada
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exceptions table

    Hello Jon,

    Because all the fields in Table2 are also present in Table1 there should be no NULL (unless I did not understand your approach).

    My goal is to return records from Table1 that are not in Table2.

    Table1 is the master table that contains all records from Table2 and many more records.

    Regards,
    Rajesh

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,719
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Exceptions table

    Did you try using the Unmatched Query Wizard? You should be able to use it to construct a query that returns records from table1 that are not in table 2 and whose sql resembles what was posted.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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