Selecting Unmatched Records Based On Multiple Flds

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting Unmatched Records Based On Multiple Flds

Author

Topic

Clif001
Starting Member

4 Posts

Posted - 2004-07-21 : 13:39:10

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.

How do do this?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-21 : 14:06:38

errr, when you ran it WITH EXISTS did it return all the rows in both tables?????? If so, then both queries worked as expected.

humdedumdedumdedum

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Clif001
Starting Member

4 Posts

Posted - 2004-07-22 : 07:32:18

Actually they didn't. I expected the NOT EXISTS to return the one record from Table2 that did not match the five fields.

Perhaps I should clarify.

I have two tables, Table1 and Table2. Each of these table contains several fields. Of these fields, five fields match in each table.

What I would like is a query which will display the records in Table2 which do not match the five fields in Table1.

For example:

Table 1 contains the following:
Field1: George
Field2: Washington
Field3: Virginia
Field4: 1789
Field5: 1797

Field1: John
Field2: Adams
Field3: Massachusetts
Field4: 1797
Field5: 1801

Field1: Thomas
Field2: Jefferson
Field3: Virginia
Field4: 1801
Field5: 1809

Table2 contains the following:
Field1: George
Field2: Washington
Field3: Virginia
Field4: 1789
Field5: 1797

Field1: John
Field2: Adams
Field3: Massachusetts
Field4: 1797
Field5: 1801

Field1: Thomas
Field2: Jefferson
Field3: Virginia
Field4: 1801
Field5: 1809

Field1: James
Field2: Madison
Field3: Virginia
Field4: 1809
Field5: 1817

What I need is a query that will display:
Field1: James
Field2: Madison
Field3: Virginia
Field4: 1809
Field5: 1817

Because it is in Table2, but not in Table1

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-22 : 07:56:38

Do this and we'll really be able to help you out. Give us some create table/insert table statements with sample data. And, give us your query. We can then figure it out for you pretty quickly. Otherwise, we have to write all this ourselves (messy, messy).

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Clif001
Starting Member

4 Posts

Posted - 2004-07-23 : 07:12:53

My co-worker decided on another tact.

Thanks for the help, though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources