Two-way Unmatched Records Query?

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

I have two table linked on the field 'Range' and the following query
returns 'Field' values in one table that aren't in the other.

SELECT DISTINCT tblderivative_11thNov07.Range
FROM tblderivative_11thNov07 LEFT JOIN tblSector ON
tblderivative_11thNov07.Range = tblSector.Range
WHERE (((tblSector.Range) Is Null));

Is it possible to amend the query to *also* do the same thing but the
other way round i.e. values in tblSector but not in tblDerivative_11thNov07?

But also somehow to include a field which indicates which table is
missing which value?

Any help greatly appreciated....thanks, Jason
 
SELECT DISTINCT tblderivative_11thNov07.Range,
"Not in tblSector" As Comment
FROM tblderivative_11thNov07 LEFT JOIN tblSector ON
tblderivative_11thNov07.Range = tblSector.Range
WHERE tblSector.Range Is Null
UNION
SELECT DISTINCT tblSection.Range.
"Not in tblderivative_11thNov07" As Comment
FROM tblderivative_11thNov07 RIGHT JOIN tblSector ON
tblderivative_11thNov07.Range = tblSector.Range
WHERE tblderivative_11thNov07.Range Is Null
ORDER BY 2, 1
 
Jay said:
I have two table linked on the field 'Range' and the following query
returns 'Field' values in one table that aren't in the other.

SELECT DISTINCT tblderivative_11thNov07.Range
FROM tblderivative_11thNov07 LEFT JOIN tblSector ON
tblderivative_11thNov07.Range = tblSector.Range
WHERE (((tblSector.Range) Is Null));

Is it possible to amend the query to *also* do the same thing but the
other way round i.e. values in tblSector but not in tblDerivative_11thNov07?

But also somehow to include a field which indicates which table is
missing which value?
Click to expand...


SELECT "11Nov07" As Tbl, tblderivative_11thNov07.Range
FROM tblderivative_11thNov07 LEFT JOIN tblSector
ON tblderivative_11thNov07.Range = tblSector.Range
WHERE tblSector.Range Is Null
UNION
SELECT "Sector", tblSector.Range
FROM tblderivative_11thNov07 RIGHT JOIN tblSector
ON tblderivative_11thNov07.Range = tblSector.Range
WHERE tblderivative_11thNov07.Range Is Null
 
*Man y* thanks for this Doug. I've never written a Union query - I know
'of them' as I've been teaching myself SQL, so to have a real-world
example is great. I've also not come across 'Comments' so this is a
fantastic learning example!

Does the 'ORDER BY 2, 1' refer to each query?

Regards.......Jason
 
Back
Top