how to Compare two record - 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 2005 Forums

Transact-SQL (2005)

how to Compare two record

Author

Topic

hamid.y
Starting Member

22 Posts

Posted - 2010-03-01 : 04:51:12

i need to know how to check records of two tables with the same fields are equal or not.
can you help me?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 05:05:01

select t1.col1,t2.col1,case when t1.col1=t2.col1 then 'same' else 'not same' end from table1 as t1
left join table2 as t2 on t1.keycol=t2.keycol

Madhivanan

Failing to plan is Planning to fail

hamid.y
Starting Member

22 Posts

Posted - 2010-03-01 : 05:36:02

maybe I could not mention my problem clearly.
i have two table with the same rows.
i want find rows which is the same with other table's records.
------------------
table1:

T1ID | name | color
-----------------
1 x Blue
2 y Red
3 z Black

------------------
Table2:
T2ID | name | color
-------------------
1 x Blue
3 z Black
5 w Gray

i want the following result out of my query

ID | name | color
-----------------
1 x Blue
3 z Black

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 05:37:54

select * from table1 as t1 where eixsts
(select * from table2 where T2ID=t1.T1ID and name=t1.name and color=t1.color)

Madhivanan

Failing to plan is Planning to fail

hamid.y
Starting Member

22 Posts

Posted - 2010-03-01 : 05:52:26

thanks a lot for your patience and useful answer.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 06:04:45

[code]
SELECT *
FROM table1 T1
JOIN table2 T2
ON T1.T2ID=T2.T1ID
AND T1.name=T2.name
AND T1.color=T2.color

SELECT T1ID, name, color
FROM table1
INTERSECT
SELECT T1ID, name, color
FROM table2
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 07:15:55

quote:

Originally posted by ms65g

     
SELECT * 
  FROM table1 T1
  JOIN table2 T2
    ON T1.T2ID=T2.T1ID 
   AND T1.name=T2.name 
   AND T1.color=T2.color   
   
SELECT T1ID, name, color
  FROM table1
INTERSECT
SELECT T1ID, name, color
  FROM table2

When you use JOIN, explicitely type the table alias. * should be t1.*

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-01 : 07:28:28

INTERSECT should also work.
http://weblogs.sqlteam.com/jeffs/archive/2007/05/02/60194.aspx

select * from table1
INTERSECT
select * from table2

No, you're never too old to Yak'n'Roll if you're too young to die.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 07:46:42

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

SELECT *
FROM table1 T1
JOIN table2 T2
ON T1.T2ID=T2.T1ID
AND T1.name=T2.name
AND T1.color=T2.color

SELECT T1ID, name, color
FROM table1
INTERSECT
SELECT T1ID, name, color
FROM table2

When you use JOIN, explicitely type the table alias. * should be t1.*

Madhivanan

Failing to plan is Planning to fail

In this case, or should be t2.*

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-01 : 08:16:48

Oh sorry ms65g!
I have not seen that your post has already shown the solution with INTERSECT.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 08:16:55

quote:
Originally posted by ms65g

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

SELECT *
FROM table1 T1
JOIN table2 T2
ON T1.T2ID=T2.T1ID
AND T1.name=T2.name
AND T1.color=T2.color

SELECT T1ID, name, color
FROM table1
INTERSECT
SELECT T1ID, name, color
FROM table2

When you use JOIN, explicitely type the table alias. * should be t1.*

Madhivanan

Failing to plan is Planning to fail

In this case, or should be t2.*

How does it matter whether t1 or t2?

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 08:33:23

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

quote:
Originally posted by madhivanan

quote:
Originally posted by ms65g

SELECT *
FROM table1 T1
JOIN table2 T2
ON T1.T2ID=T2.T1ID
AND T1.name=T2.name
AND T1.color=T2.color

SELECT T1ID, name, color
FROM table1
INTERSECT
SELECT T1ID, name, color
FROM table2

When you use JOIN, explicitely type the table alias. * should be t1.*

Madhivanan

Failing to plan is Planning to fail

In this case, or should be t2.*

How does it matter whether t1 or t2?

Madhivanan

Failing to plan is Planning to fail

Do you want see the NOTHING word?!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 09:07:29

<<
Do you want see the NOTHING word?!
>>

Are you agree with me that when you use JOINs you need to qualify the table alias for the columns?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-01 : 09:13:36

The only thing ms65g means was:
It is ok to take t1.* and ALSO it would be ok to take t2.*

So you two can stop this...

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 09:17:09

quote:
Originally posted by webfred

The only thing ms65g means was:
It is ok to take t1.* and ALSO it would be ok to take t2.*

So you two can stop this...

No, you're never too old to Yak'n'Roll if you're too young to die.

I am stopping

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 10:02:08

quote:
Originally posted by madhivanan

select * from table1 as t1 where eixsts
(select * from table2 where T2ID=t1.T1ID and name=t1.name and color=t1.color)

Madhivanan

Failing to plan is Planning to fail

You used EXISTS predicate. Why did not use IN predicate? It is not simpler and shorter?

select * from table1 as t1 where color IN(
(select color from table2 where T2ID=t1.T1ID and name=t1.name)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-01 : 10:11:58

If there are a lot of entries then my experience says: it is badly slowing down the performance.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 01:32:54

quote:
Originally posted by ms65g

quote:
Originally posted by madhivanan

select * from table1 as t1 where eixsts
(select * from table2 where T2ID=t1.T1ID and name=t1.name and color=t1.color)

Madhivanan

Failing to plan is Planning to fail

You used EXISTS predicate. Why did not use IN predicate? It is not simpler and shorter?

select * from table1 as t1 where color IN(
(select color from table2 where T2ID=t1.T1ID and name=t1.name)

Dont think that it is simpler and shorter until you test with large set of data

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources