non duplicates - SQL Server Forums

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

Transact-SQL (2000)

non duplicates

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-08-06 : 04:34:09

hi guys, Im sure I've asked this before but can't remember.

I have 2 tables with transactions in , I am trying to compare the tables to find out with transaction values are different in the 2 tables.
eg.
table one :

ID date value
1 1/4/4 100
2 2/4/4 10
3 2/4/4 90

table two

ID date value
1 1/4/4 100
2 2/4/4 10
3 2/4/4 100

as you can see ID 3 has different values, how can I show these rows ?

thank you ,
Jamie

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-06 : 04:37:01

would this work for you?

select * from Table1
Where ID not in (select T1.ID from table1 T1 inner join table2 T2 on T1.ID = T2.ID and T1.Value=T2.Value)

Go with the flow & have fun! Else fight the flow :)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-06 : 04:37:33

select a.*
from tableone a
where not exists (select b.* from table2 where a.id = b.id and a.date = b.date and a.value = b.value)

And you can check it the other way round too (just in case theres entries in one table that aren't in the other)

Duane.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-06 : 04:40:34

hey duane i always forget what's the difference in working between not in (i get that :)) and not exists?

Go with the flow & have fun! Else fight the flow :)

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-08-06 : 05:12:01

the id's exisits in all tables its the values that have changed.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-08-06 : 05:21:34

Well it should work then :)

Duane.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-08-06 : 05:30:06

ok thanks.
I just need to look throught the data to make sur it is correct.

thank you.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-08-06 : 13:22:54

quote:
Originally posted by spirit1

hey duane i always forget what's the difference in working between not in (i get that :)) and not exists?

A query using NOT EXISTS is usually much more efficient than one using NOT IN. The exists test can simply do a seek, while the in needs to instantiate the whole set of values, then scan for the target (a very messy process in comparison).

-PatP

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-06 : 14:14:57

Another good reason to choose EXISTS over IN: If the inner query returns NULL as one of the values, the query bombs and will return no rows! Look at Page47's example about halfway down this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28542

OS

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-06 : 14:16:45

quote:
Originally posted by Pat Phelan

quote:
Originally posted by spirit1

hey duane i always forget what's the difference in working between not in (i get that :)) and not exists?
A query using NOT EXISTS is usually much more efficient than one using NOT IN. The exists test can simply do a seek, while the in needs to instantiate the whole set of values, then scan for the target (a very messy process in comparison).

-PatP

The real answer is...it depends...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-06 : 14:36:41

[code]
USE Northwind
GO

CREATE TABLE myTable99([ID] int, [date] datetime, value int)
CREATE TABLE myTable00([ID] int, [date] datetime, value int)
GO

INSERT INTO myTable99([ID], [date], value)
SELECT 1, '1/4/2004', 100 UNION ALL
SELECT 2, '2/4/2004', 10 UNION ALL
SELECT 3, '2/4/2004', 90

INSERT INTO myTable00([ID], [date], value)
SELECT 1, '1/4/2004', 100 UNION ALL
SELECT 2, '2/4/2004', 10 UNION ALL
SELECT 3, '2/4/2004', 100
GO

SELECT *
FROM myTable99
WHERE [ID] NOT IN (SELECT T1.[ID]
FROM myTable99 T1
INNER JOIN myTable00 T2
ON T1.[ID] = T2.[ID]
AND T1.[date] = T2.[date]
AND T1.value =T2.value)
GO

INSERT INTO myTable99([ID], [date], value)
SELECT 4, '1/4/2004', Null

INSERT INTO myTable00([ID], [date], value)
SELECT Null, '1/4/2004', 100
GO

-- I did not know this...damn it's time for vacation

SELECT *
FROM myTable99
WHERE [ID] NOT IN (SELECT T1.[ID]
FROM myTable99 T1
LEFT JOIN myTable00 T2
ON T1.[ID] = T2.[ID]
AND T1.[date] = T2.[date]
AND T1.value =T2.value)
GO

SELECT *
FROM myTable99 T1
WHERE NOT EXISTS ( SELECT *
FROM myTable00 T2
WHERE T1.[ID] = T2.[ID]
AND T1.[date] = T2.[date]
AND T1.value = T2.value)
GO

DROP TABLE myTable99
DROP TABLE myTable00
GO

[/code]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources