Software

Ask something
Discussion Locked

Selecting Unmatched Records Based On Multiple Fields

0 helpful
7 replies

by bardwell_clifton_j

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I need to list all the records in Table2 which don't have matching field values in Table1.

This the the exact opposite of what I need:
SELECT DISTINCT
Field1,
Field2,
Field3,
Field4,
Field5
FROM
[Table1]
WHERE EXISTS(
SELECT DISTINCT
FieldA,
FieldB,
FieldC,
FieldD,
FieldE
FROM
[Table2]
)

The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.

What I need, however, is the exact opposite.

I tried the above using NOT EXISTS but I get no records at all.

Anyone know how I would this?

7 replies

by dan cooperstock

What you need is more like this:

select * from table2
where not exists
(select * from table1
where table1.field1 = table2.field1
and table1.field2 = table2.field2
and table1.field3 = table2.field3
and table1.field4 = table2.field4
and table1.field5 = table2.field5)

by bbachmann

This can be handy as well:

select field1, field2, field3, field4, field5
from table2
minus
select field1, field2, field3, field4, field5
from table1;

by bucky kaufman (mcsd)

Using JOINS and subqueries are not always a good idea - especially since you don't know which proprietary SQL platform will run this.

by mlwonio

In Access I use the simple unmatched records query wizard (from the new query button) which uses a single field, then change the design by modifying the join to include all the desired fields.

by bucky kaufman (mcsd)

I think the answer to your question lies with the fundamentals of DML (Data *Manipulation* Language) vs. DDL (Data Definition Language). You're trying to accomplish, through DML what is probably better done through DDL.

DDL is different, from application to application, so it's tough to recommend a syntax. But, if you look at the manual for your particular package for DDL (not DML) commands, it should tell you how to compare two structures like this.

by durhamgeoff

SELECT WhateverYouWant
FROM Table2
LEFT
JOIN Table1
ON FIeldsYouWantToJoinOn
WHERE (
Table1.Field1YouWantToJoinOn IS NULL
AND
Table2.Field1YouWantToJoinOn IS NULL
etc
)

This discussion is closed to new replies.