Compare Two Tables, Select Unique Records

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)

Compare Two Tables, Select Unique Records

Author

Topic

Arnold
Starting Member

5 Posts

Posted - 2005-02-02 : 18:47:31

I have two tables with identical structures:

(1)Feedback_Compilation_Update
(2)FeedbackLogs_Update_From_Access

Two of the fields in these tables are "SSN" and "logtime".

I want to select all of the rows in Table(1) that don't have the same SSN and logtime combination as in Table(2).

Here is the code I am using:

SELECT DISTINCT *
FROM [Feedback_Compilation_Update]
WHERE NOT EXISTS
(SELECT *
FROM [FeedbackLogs_Update_From_Access]
WHERE (logtime = [Feedback_Compilation_Update].[logtime]) AND
(SSN = [Feedback_Compilation_Update].[SSN]))

This code doesn't work. Every row in Table(1) is being selected; that is, rows in Table(1) with an identical SSN and logtime combination as rows in Table(2) aren't being excluded.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-02 : 18:52:51

Maybe this:

SELECT *
FROM Feedback_Compilation_Update t1
LEFT OUTER JOIN FeedbackLogs_Update_From_Access t2
ON t1.SSN = t2.SSN AND t1.logtime = t2.logtime
WHERE t2.SSN IS NULL AND t2.logtime IS NULL

Tara

jhermiz

3564 Posts

Posted - 2005-02-02 : 18:54:17

Or this...

SELECT Field1, Field2
FROM Table1 WHERE Field1 NOT IN (SELECT Field1, Field2 FROM Table2)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-02-02 : 18:54:24

You need to alias the tables, and expilictly used them

ie


SELECT DISTINCT *
FROM [Feedback_Compilation_Update] as X
WHERE NOT EXISTS
(SELECT *
FROM [FeedbackLogs_Update_From_Access] as Y
WHERE (Y.logtime = X.[logtime]) AND
(Y.SSN = X.[SSN]))

PS: Not parsed....

DavidM

"Always pre-heat the oven"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-02 : 18:56:18

Here is what I used to test:



SET NOCOUNT ON

CREATE TABLE Table1 (SSN char(9), logtime datetime)
CREATE TABLE Table2 (SSN char(9), logtime datetime)

INSERT INTO Table1 VALUES (555443333, 'Jan 1 2005')
INSERT INTO Table2 VALUES (555443333, 'Jan 1 2005')
INSERT INTO Table1 VALUES (555442222, 'Jan 1 2005')
INSERT INTO Table1 VALUES (555442222, 'Jan 2 2005')
INSERT INTO Table1 VALUES (555441111, 'Jan 10 2005')

SELECT t1.*
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.SSN = t2.SSN AND t1.logtime = t2.logtime
WHERE t2.SSN IS NULL AND t2.logtime IS NULL

DROP TABLE Table1
DROP TABLE Table2

Tara

Arnold
Starting Member

5 Posts

Posted - 2005-02-03 : 12:50:20

Thank you very much, everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources