SQL Query to compare

Question

Hi,

I am working on an Access database and in it I need to extract several reports. For one of them I need to compare two lists of numbers, List1 and List2(I get both from separate queries) and display only those numbers that are in List1 but not in List2.

Can anyone help please? Thank you.

Answer 1

SELECT some_value
FROM table1
WHERE some_value not in ( select some_value from table2);

Answer 2

SELECT  *
FROM    (
        SELECT  number
        FROM    …
        ) query1
WHERE   query1.number NOT IN
        (
        SELECT  number
        FROM    …
        ) /* query2 */

Answer 3

You can do this using a LEFT OUTER JOIN

SELECT T1.val
FROM table1 T1
LEFT OUTER JOIN table2 T2 ON T1.Val = T2.Val
WHERE T2.VAL IS NULL;

Answer 4

Others have provided some good answers in SQL, but you may wish to use some of the built-in functionality of Access.

When I'm in the query screen (in Access XP/2002, but it should be the same for Access 2003), I can click New and then there is an option for Find Unmatched Query Wizard. This will walk you through a series of dialog boxes that help you set up the query you are looking for. You will need to have "List1" and "List2" (from your example) already defined before going through this wizard.

After you have this set up, you'll be able to see how Access created the query, which is a great way to learn.