Comparing values within the same table

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

General SQL Server Forums

New to SQL Server Programming

Comparing values within the same table

Author

Topic

kali79
Starting Member

1 Post

Posted - 2014-05-01 : 10:13:47

Hi,

I would like to compare some values in two columns which are in the same table. I want to check that there are no differences between the values if the ID is Test1 and Test2

Example table

ID Value1 Value 2
TEST1 House Tango
TEST2 House Tango

with test as (
select * from ExampleTable where ID= 'TEST'
),
test2 as (
select * from ExampleTable where ID= 'TEST2'
)
select t2.ID, t2.Value1, t2.Value2 old, t.ID, t.Value1, t.Value2 AS new
from
test t full
outer join test2 t2
on t.Value1 = t2.Value1
and t.Value2 = t2.Value2
where t.Value1 is null
OR t.Value2 is null

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-01 : 10:29:30

I often use EXCEPT or INTERSECT for this type of thing:


with 
ExampleTable(ID, Value1, Value2) as (
	select * from (values	
		('TEST1', 'House', 'Tango'),
		('TEST2', 'House', 'Tango')
	) a(b,c,d)
),

test as (
select * from ExampleTable where ID= 'TEST1'
),
test2 as (
select * from ExampleTable where ID= 'TEST2'
)

(select  Value1, Value2 from test except select Value1, Value2 from test2)
union
(select  Value1, Value2 from test2 except select Value1, Value2 from test)

If this query produces no rows, the derived tables are identical

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-01 : 10:32:50

Usually when I am doing ad-hoc tests to as part of development, I use the EXCEPT construct, like this:

-- First check if there is any test1 that does not match corresponding test2 rows.
SELECT value1, value2 FROM Table1 WHERE id = 'test1'
EXCEPT
SELECT value1, value2 FROM Table1 WHERE id = 'test2'

-- and then the other way around.
SELECT value1, value2 FROM Table1 WHERE id = 'test2'
EXCEPT
SELECT value1, value2 FROM Table1 WHERE id = 'test1'

This of course has some limitations. For example, if you had two test1 rows and only one test2 row with the save value1 and value2, you wouldn't catch that.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-01 : 10:39:49

quote:
Originally posted by James K

Usually when I am doing ad-hoc tests to as part of development, I use the EXCEPT construct, like this:
-- First check if there is any test1 that does not match corresponding test2 rows.
SELECT value1, value2 FROM Table1 WHERE id = 'test1'
EXCEPT
SELECT value1, value2 FROM Table1 WHERE id = 'test2'

-- and then the other way around.
SELECT value1, value2 FROM Table1 WHERE id = 'test2'
EXCEPT
SELECT value1, value2 FROM Table1 WHERE id = 'test1'
This of course has some limitations. For example, if you had two test1 rows and only one test2 row with the save value1 and value2, you wouldn't catch that.

Yes, you're right of course. Probably good to compare the rowcounts in the CTEs test and test2 first. They should be equal.

Another approach is to do an intersection of the two tables (minus the ID column). If the result has the same number of rows as each the starting tables, they are identical (both as bags and sets). If not, you know that one of the tables has one or more duplicate rows (though you still don't know which one!)

ke.neelima
Starting Member

6 Posts

Posted - 2014-05-02 : 03:41:34

I couldnt understand the question clearly. But if my understanding is correct, we can even just use a self join and check the result..

Select t1.id from test t1 -- select watever columns u want
join test t2 on t1.val1 = t2.val1 and t1.val2 = t2.val2
where t1.col1 = t2.col2 -- add watever conditions u want here

Neelima

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources