URGENT: Query to view data from table A minus table B.
Hi, I would like to make a query that will give me rows from table A excluding the rows in table B. Both tables have the same structure.
I know I can use something like below, but I need to do a join on each field (I have 20 fields). The problem I am having is that some of these fields may be null on various rows, so a comparison of A.field1 = B.field1 does not work.
Example:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (Table2.d = Table1.d) AND (Table2.c =
Table1.c) AND (Table2.b = Table1.b) AND (Table2.a = Table1.a) AND
(Table2.Field1= Table1.Field1)
WHERE (((Table1.Field1) Is Null));
I am using MS Access 2000, and can use VBA to help do this if necessary - but I would like to avoid using Access specific commands/features since I would like to migrate to another system in the near future. Can someone provide me a fairly simple solution.
When you do your join in the above query, you can use the nz function (in access, but there is an equivalent in sql server and oracle) to convert the null value.
Wrap the comparison values in the "nz" function:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (nz(Table2.d) = nz(Table1.d)) AND (nz(Table2.c) =
nz(Table1.c)) AND (nz(Table2.b) = nz(Table1.b)) AND (nz(Table2.a) = nz(Table1.a)) AND
(nz(Table2.Field1)= nz(Table1.Field1))
WHERE (Table1.Field1 Is Null);
Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Wrap the comparison values in the "nz" function:
SELECT Table2.*
FROM Table2 LEFT JOIN Table1 ON (nz(Table2.d) = nz(Table1.d)) AND (nz(Table2.c) =
nz(Table1.c)) AND (nz(Table2.b) = nz(Table1.b)) AND (nz(Table2.a) = nz(Table1.a)) AND
(nz(Table2.Field1)= nz(Table1.Field1))
WHERE (Table1.Field1 Is Null);