November 27, 2007 at 12:17 pm
I have 3 tables 1,2 and 3. They all contain identical columns. How can i join these tables in order to find ONLY the differences between the tables?
November 27, 2007 at 12:24 pm
What differences are you looking for?
Differences in DDL?
or differences in data?
November 27, 2007 at 12:27 pm
NEI (Not Enough Information). What differences are you looking for between the tables. To help you, we really need to have the DDL for your tables, some sample data, and what you expect to see from that data. Also, what have you tried so far to solve this yourself. Knowing what you have done could also help us point you in the right direction.
😎
November 27, 2007 at 2:28 pm
If you were on 2005, I'd suggest the EXCEPT clause:
select * from 1
EXCEPT
select * from 2
of course - you're going to have to do this 6 times to get all of the differences.
Of course - if you really ARE using 2000, this is of no use to you. DDL would then be required to help you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 27, 2007 at 2:40 pm
You can use a full outer join like the following:
SELECT ISNULL(A.KeyCol, B.KeyCol)
,CASEWHEN ISNULL(A.NonKeyCol1,'') = ISNULL(B.NonKeyCol1,'')
THEN 'OK'
ELSE ISNULL(A.NonKeyCol1,' ')
END
,CASEWHEN ISNULL(A.NonKeyCol2,'') = ISNULL(B.NonKeyCol2,'')
THEN 'OK'
ELSE ISNULL(A.NonKeyCol2,' ')
END
FROMTableA A
FULL OUTER JOIN TableB B ON A.KeyCol = B.KeyCol
WHERE NOT (
ISNULL(A.NonKeyCol1,'') = ISNULL(B.NonKeyCol1,'')
AND ISNULL(A.NonKeyCol2,'') = ISNULL(B.NonKeyCol2,'')
)
P.S. for some reason the code gets corrupted when enclosed in tags like "code" or "quote" seems to be a forum bug?
SELECT ISNULL(A.KeyCol, B.KeyCol)
,CASEWHEN ISNULL(A.NonKeyCol1,'') = ISNULL(B.NonKeyCol1,'')
THEN 'OK'
ELSE ISNULL(A.NonKeyCol1,' ')
END
,CASEWHEN ISNULL(A.NonKeyCol2,'') = ISNULL(B.NonKeyCol2,'')
THEN 'OK'
ELSE ISNULL(A.NonKeyCol2,' ')
END
FROMTableA A
FULL OUTER JOIN TableB B ON A.KeyCol = B.KeyCol
WHERE NOT (
ISNULL(A.NonKeyCol1,'') = ISNULL(B.NonKeyCol1,'')
AND ISNULL(A.NonKeyCol2,'') = ISNULL(B.NonKeyCol2,'')
)
Best Regards,
Chris Büttner
November 27, 2007 at 3:38 pm
As Lynn stated, not enough information! On top of the table DDL and the code that you've already tried (if any), we need to know if you want to find non-duplicating rows based off of a unique key or if you need to consider all column values.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply