Make a query to display unmatched data

Question

I m having difficulty making a query that displays unmatched data in between 2 tables. The tables are:

Employee data                   Salary data
ID  Holidays               ID      Holiday
1    10                    0       10
2     8                    1        5
3     5                    2        8
4     7                    3        5
5     8                    7        6
6     5                    8        9
7     6                    9        2
8     9                    10       3

the primary key is ID for both tables.

I want my query result to contain all the values that does not match in both tables. The type of output i want is something like this:

ID    Holiday
0       10
1       10
1        5
4        7 
5        8
6        5
9        2
10       3

I tried using unmatched query wizard but that only compares ID, not the Holiday column. Please help me!

Answer 1

You could use a union of two exists queries:

SELECT ID, Holiday FROM Employee as e
WHERE NOT EXISTS (SELECT 1 FROM Salary as s WHERE s.ID = e.ID AND s.Holiday = e.Holiday)
UNION ALL
SELECT ID, Holiday FROM Salary as s
WHERE NOT EXISTS (SELECT 1 FROM Employee as e WHERE e.ID = s.ID AND e.Holiday = s.Holiday);