Design a query to display rows that are not equal

Question

I have 2 tables

Table 1:

Table 2:

I want my query to display all rows that are uncommon in between the 2 tables in this format:

Expected Result:

Any Ideas would be highly appreciated! I tried using UNION as suggested before, but it did not give the result I want.

Answer 1

I can't do this in 1 query, but I can do it in several!!

Firstly, create a union query (Query1) that combines the items in table 1 that don't appear in table 2 and also those items in table 2 that don't appear in table 1. Your SQL should look like:

SELECT T2.ID
FROM Table2 T2 LEFT JOIN Table1 T1 ON T2.[ID] = T1.[ID]
WHERE (((T1.ID) Is Null))
UNION SELECT T1.ID
FROM Table1 T1 LEFT JOIN Table2 T2 ON T1.[ID] = T2.[ID]
WHERE (((T2.ID) Is Null));

Your second query (Query2) is then based on this query, joined to both Table1 and Table2, showing all records from the query and any records from these tables where the IDs match. Your SQL should look like this:

SELECT Q1.ID, T1.holiday AS H1, T2.Holiday AS H2
FROM Table2 T2 RIGHT JOIN (Table1 T1 RIGHT JOIN Query1 Q1 ON T1.ID = Q1.ID) ON T2.ID = Q1.ID;

Next, create a union query (Query3) based on Table1 and Table2:

SELECT T1.ID FROM Table1 T1
UNION SELECT T2.ID FROM Table2 T2;

Then create a fourth query (Query4) based on Query3, joined to Table1 and Table2, where Table1.Holiday<>Table2.Holiday:

SELECT Q3.ID, T1.holiday AS H1, T2.Holiday AS H2
FROM Table2 T2 RIGHT JOIN (Table1 T1 RIGHT JOIN Query3 Q3 ON T1.ID = Q3.ID) ON T2.ID = Q3.ID
WHERE T1.Holiday<>T2.Holiday

Finally, build a union query on Query2 and Query4:

SELECT ID, H1, H2 FROM Query2
UNION SELECT ID, H1, H2 FROM Query4

This should then give you your desired output:

There may be an easier way of doing this within SQL though.......

Regards,