Finding missing records

[Scott Cravens]

I'm quite new to SQL. I would like to return a set of records that aren't found in the results of another query.

Here is the first query that returns a list of ALL POSSIBLE combinations:

SELECT
A.[FIELD_1],
B.[FIELD_2],
C.[FIELD_3]
FROM
T1 AS A,
T2 AS B,
T3 AS C;

Here is the second query that returns a list of ALL USED combinations:

SELECT
A.[FIELD_1],
B.[FIELD_2],
C.[FIELD_3]
FROM
T4 AS D
INNER JOIN T5 AS A
ON D.[FIELD_4] = A.[FIELD_4]
INNER JOIN T6 AS C
ON D.[FIELD_5] = C.[FIELD_5]
INNER JOIN T7 AS B
ON C.[FIELD_6] = B.[FIELD_6];

What I'm wondering if there is a way to modify the first query to return ALL POSSIBLE combinations that don't exist in the results of the second query.

[Rixxe]

Morning,

You could use 'not equal' '<>'
So:
Where Query1 <> Query2

[ck9663]

Use FULL OUTER JOIN and check if one of the link-columns is NULL. Make sure those are not Nullable to begin with.

Good Luck!!!

~~ CK

[sandeep M]

TRY THIS


SELECT
A.[FIELD_1],
B.[FIELD_2],
C.[FIELD_3]
FROM
T1 AS A,
T2 AS B,
T3 AS C;

Except

SELECT
A.[FIELD_1],
B.[FIELD_2],
C.[FIELD_3]
FROM
T4 AS D
INNER JOIN T5 AS A
ON D.[FIELD_4] = A.[FIELD_4]
INNER JOIN T6 AS C
ON D.[FIELD_5] = C.[FIELD_5]
INNER JOIN T7 AS B
ON C.[FIELD_6] = B.[FIELD_6];