Select from Two Tables each Column that is Different  
Author Message
rfdjr1





PostPosted: Tue Apr 18 14:27:32 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different

Oops - posted this to the wrong ng. This is more appropriate:

I have two tables, Table A and Table B. For the sake of this, we will
say that their DDL is identical. They have an associated key, ID.

I need to return for each ID only those columns that are different.

A plain English statement would look like this:
select ID, Column1 from Table A if A.Column1 <> B.Column1.
select ID, Column2 from TableB if A.Column2 <> B.Column2.
. . .

Of course, I want only a single return set with all of the columns that
do not pass the comparison.

What methods of accomplishing this do you recommend?

Thanks

-tom

SQL Server91  
 
 
David





PostPosted: Tue Apr 18 14:27:32 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different
> Oops - posted this to the wrong ng. This is more appropriate:
>
> I have two tables, Table A and Table B. For the sake of this, we will
> say that their DDL is identical. They have an associated key, ID.
>
> I need to return for each ID only those columns that are different.
>
> A plain English statement would look like this:
> select ID, Column1 from Table A if A.Column1 <> B.Column1.
> select ID, Column2 from TableB if A.Column2 <> B.Column2.
> . . .
>
> Of course, I want only a single return set with all of the columns that
> do not pass the comparison.
>
> What methods of accomplishing this do you recommend?
>
> Thanks
>
> -tom

I think you'll have to use two separate operations. Compare the tables
to determine where the differences are, then decide what columns to
display based on that. A SQL query always returns a fixed number of
columns and even though you can construct that query dynamically you'll
still have to do the compare first. Example:

SELECT a.id, a.col1, a.col2, a.col3,
CASE WHEN a.col1 <> b.col1 THEN 'COL1' END AS diff1,
CASE WHEN a.col2 <> b.col2 THEN 'COL2' END AS diff2,
CASE WHEN a.col3 <> b.col3 THEN 'COL3' END AS diff3
FROM a
JOIN b
ON a.id = b.id ;

This assumes no nulls.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

 
 
tom





PostPosted: Tue Apr 18 14:48:20 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different >I think you'll have to use two separate operations. Compare the tables
>to determine where the differences are,

That works pretty well, and certainly provides the needed output.
Thank you for posting.

Is there a way to accomplish this without the initial select? I will
have to clean up the unwanted select results. I suppose I could just
throw it into a table, and perform the clean up on that table.

Thanks
-tom

 
 
tom





PostPosted: Tue Apr 18 15:05:20 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different >Is there a way to accomplish this without the initial select?

Answered my own question - it works fine without the initial select.

Thank you very much for your time.

-tom

 
 
--CELKO--





PostPosted: Tue Apr 18 15:05:49 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different >> I need to return for each ID only those columns that are different. <<

CREATE TABLE Alpha
(vague_id INTEGER NOT NULL PRIMARY KEY,
foobar CHAR(5) NOT NULL);

CREATE TABLE Beta
(vague_id INTEGER NOT NULL PRIMARY KEY,
foobar CHAR(5) NOT NULL);

SELECT Alpha.vague_id, Alpha.foobar, Beta.foobar
FROM Alpha
FULL OUTER JOIN
Beta
ON Alpha.vague_id = Beta.vague_id
AND Alpha.foobar <> Beta.foobar;

 
 
David





PostPosted: Tue Apr 18 15:03:54 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different Here goes:

CREATE TABLE a (id INT PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL,
col3 INT NOT NULL);
CREATE TABLE b (id INT PRIMARY KEY, col1 INT NOT NULL, col2 INT NOT NULL,
col3 INT NOT NULL);

INSERT INTO a (id,col1,col2,col3)
VALUES (1,100,100,100);
INSERT INTO a (id,col1,col2,col3)
VALUES (2,200,200,200);
INSERT INTO a (id,col1,col2,col3)
VALUES (3,300,300,300);

INSERT INTO b (id,col1,col2,col3)
VALUES (1,100,111,100);
INSERT INTO b (id,col1,col2,col3)
VALUES (2,200,211,211);
INSERT INTO b (id,col1,col2,col3)
VALUES (3,300,300,300);




'SELECT a.id'+
MAX(CASE WHEN a.col1 <> b.col1 THEN ',a.COL1' ELSE '' END)+
MAX(CASE WHEN a.col2 <> b.col2 THEN ',b.COL2' ELSE '' END)+
MAX(CASE WHEN a.col3 <> b.col3 THEN ',a.COL3' ELSE '' END)+
' FROM a JOIN b ON a.id = b.id
WHERE a.col1<>b.col1 OR a.col2<>b.col2 OR a.col3<>b.col3'
FROM a
JOIN b
ON a.id = b.id ;



--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--


 
 
Alexander





PostPosted: Tue Apr 18 15:10:15 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different let's tweak David's approach a little bit:

SELECT a.id, a.col1, a.col2, a.col3,
CASE WHEN a.col1 <> b.col1 THEN 'COL1' END AS diff1,
CASE WHEN a.col2 <> b.col2 THEN 'COL2' END AS diff2,
CASE WHEN a.col3 <> b.col3 THEN 'COL3' END AS diff3
FROM a
JOIN b
ON a.id = b.id
where a.col1 <> b.col1 or a.col2 <> b.col2 or a.col3 <> b.col3

 
 
tom





PostPosted: Tue Apr 18 15:23:49 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different >CREATE TABLE Alpha
>(vague_id INTEGER NOT NULL PRIMARY KEY,
> foobar CHAR(5) NOT NULL);

Awesome! I got a Celko response.
I like the way it works, too.

Life is good . . . .

:-)

-tom

 
 
tom





PostPosted: Tue Apr 18 15:27:30 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different
> 'SELECT a.id'+
> MAX(CASE WHEN a.col1 <> b.col1 THEN ',a.COL1' ELSE '' END)+

David, thank you for putting that together -

You've gotta set up a site where you accept Paypal . . .

-tom

 
 
Alexander





PostPosted: Tue Apr 18 15:29:05 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different one more approach:

create table a(i int primary key, c1 char(1), c2 char(1))
go
insert into a(i, c1, c2) values(1, 'A','B')
insert into a(i, c1, c2) values(2, 'A','C')
insert into a(i, c1, c2) values(3, 'Q','C')
insert into a(i, c1, c2) values(4, 'X','Y')
insert into a(i, c1, c2) values(5, 'X',null)
insert into a(i, c1, c2) values(6, null, 'X')
insert into a(i, c1, c2) values(7, null ,null)
go
create table b(i int primary key, c1 char(1), c2 char(1))
go
insert into b(i, c1, c2) values(1, 'A','B')
insert into b(i, c1, c2) values(2, 'A','B')
insert into b(i, c1, c2) values(3, 'B','C')
insert into b(i, c1, c2) values(4, 'B','A')
insert into b(i, c1, c2) values(5,null, 'X')
insert into b(i, c1, c2) values(6, 'X', null)
insert into b(i, c1, c2) values(7, null ,null)
go
create table n(n int)
insert into n values(1)
insert into n values(2)
go
select t1.i, t1.colname, t1.c value1, t2.c value2
from(
select i, case when n=1 then c1 else c2 end c,
case when n=1 then 'c1' else 'c2' end colname
from a cross join n
) t1
join(
select i, case when n=1 then c1 else c2 end c,
case when n=1 then 'c1' else 'c2' end colname
from b cross join n
) t2
on t1.i=t2.i and t1.colname=t2.colname
where t1.c<>t2.c
or (t1.c is null and t2.c is not null)
or (t1.c is not null and t2.c is null)
go

i colname value1 value2
----------- ------- ------ ------
2 c2 C B
3 c1 Q B
4 c1 X B
4 c2 Y A
5 c1 X NULL
5 c2 NULL X
6 c1 NULL X
6 c2 X NULL

drop table a
go
drop table b
go
drop table n
go

 
 
--CELKO--





PostPosted: Wed Apr 19 00:33:04 CDT 2006 Top

SQL Server Developer >> Select from Two Tables each Column that is Different Oh come on! I was not abusive, I did not lecture you on RDBMS basics,
etc. So that was a reallllly poor Celko response!

Sorry, but my niece took me to her apartment complex hot tub and
swimming pool (she looks great in a string bikini) and then my wife & I
had a great dinner, watched a DVD and played with one of the pit bulls.
I put $1000 in royalty & consulting money in the bank I'll do better
next time, I promise.

But I worry that you are splitting a single set (table) into multiple
tables. This is a form of redundancy that is confused with
partitioning.