SQL Apprentice Question
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?
Celko Answers
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;
Wednesday, April 19, 2006
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment