differences between 2 tables

Question

Post reply

differences between 2 tables

eseosaoregie

SSCertifiable

Points: 6889
More actions
September 20, 2011 at 5:13 am

#390440

I have 2 tables which I need to compare. Both tables consist of 2 columns, account and hcs code. In an ideal world both tables should be the same but they are prepared from different sources. For every account and hsc code in table A I need to pull out records where the same account in table B has a different hcs code or no hcs code at all. I have some data to show what I am looking at.
TABLE A
Account HCS_Code
---------------- -------------
AC_1234 ICP1
AC_1235 ICP2
AC_1236 ICP3
AC_5432 ICP4
TABLE B
Account HCS_Code
---------------- -------------
AC_1234 ICP1
AC_1235 ICP2
AC_1236 ICP7
AC_5432 ICP4
with the detail above.the output should be : AC_1236 ICP7
any ideas

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

Jayanth_Kurup

SSC-Insane

Points: 22967

September 20, 2011 at 5:17 am

#1383733

JOINS

Jayanth Kurup[/url]

mr_opto Ten Centuries Points: 1214 More actions · Answer 2

SELECT

*

FROM

TableA LEFT OUTER JOIN TableB on TableA.Account = TableB.Account

WHERE

TableA.HCS_Code != TableB.HCS_Code

OR

TableB.HCS_Code IS NULL

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Firstly, this is the correct way to post sample data: -

DECLARE @TABLEA AS TABLE (account CHAR(8), HCS_Code CHAR(4))

DECLARE @TABLEB AS TABLE (account CHAR(8), HCS_Code CHAR(4))

INSERT INTO @TABLEA

SELECT 'AC_1234', 'ICP1'

UNION ALL SELECT 'AC_1235', 'ICP2'

UNION ALL SELECT 'AC_1236', 'ICP3'

UNION ALL SELECT 'AC_5432', 'ICP4'

INSERT INTO @TABLEB

SELECT 'AC_1234', 'ICP1'

UNION ALL SELECT 'AC_1235', 'ICP2'

UNION ALL SELECT 'AC_1236', 'ICP7'

UNION ALL SELECT 'AC_5432', 'ICP4'

It's readily consumable so no-one has to put any extra work into helping you.

The question you've asked is fairly basic, and can be solved with a simple JOIN. Perhaps you should give this a read?

SELECT b.account, b.HCS_Code

FROM @TABLEA a

LEFT OUTER JOIN @TABLEB b ON a.account = b.account

WHERE a.HCS_Code <> b.HCS_Code

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 4

We need not use Left Join as we are looking for records only for the account which is present in table A. So we should use Inner Join to pull the records with diff hsc_code or records withou hsc_code

Regards,

Murali

shatrughna Ten Centuries Points: 1037 More actions · Answer 5

Hi Murli,

You can use EXCEPT here.

DECLARE @TABLEA AS TABLE (account CHAR(8), HCS_Code CHAR(4))

DECLARE @TABLEB AS TABLE (account CHAR(8), HCS_Code CHAR(4))

INSERT INTO @TABLEA

SELECT 'AC_1234', 'ICP1'

UNION ALL SELECT 'AC_1235', 'ICP2'

UNION ALL SELECT 'AC_1236', 'ICP3'

UNION ALL SELECT 'AC_5432', 'ICP4'

INSERT INTO @TABLEB

SELECT 'AC_1234', 'ICP1'

UNION ALL SELECT 'AC_1235', 'ICP2'

UNION ALL SELECT 'AC_1236', 'ICP7'

UNION ALL SELECT 'AC_5432', 'ICP4'

SELECT account,HCS_Code FROM @TABLEA

EXCEPT

SELECT account,HCS_Code FROM @TABLEB

Shatrughna

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

MuraliKrishnan1980 (9/20/2011)
We need not use Left Join as we are looking for records only for the account which is present in table A. So we should use Inner Join to pull the records with diff hsc_code or records withou hsc_code
Regards,
Murali

No, INNER is not right.

The OP said he wanted to include where there were no hcs code, which strongly implies that the account exists in table a but not in table b: -

eseosaoregie (9/20/2011)
I need to pull out records where the same account in table B has a different hcs code or no hcs code at all. I have some data to show what I am looking at.

I've bolded the relevant part.

So, if we add a record to simulate that requirement: -

DECLARE @TABLEA AS TABLE (account CHAR(8), HCS_Code CHAR(4))

DECLARE @TABLEB AS TABLE (account CHAR(8), HCS_Code CHAR(4))

INSERT INTO @TABLEA

SELECT 'AC_1234', 'ICP1'

UNION ALL SELECT 'AC_1235', 'ICP2'

UNION ALL SELECT 'AC_1236', 'ICP3'

UNION ALL SELECT 'AC_5432', 'ICP4'

UNION ALL SELECT 'NEWCODE', 'TEST'

INSERT INTO @TABLEB

SELECT 'AC_1234', 'ICP1'

UNION ALL SELECT 'AC_1235', 'ICP2'

UNION ALL SELECT 'AC_1236', 'ICP7'

UNION ALL SELECT 'AC_5432', 'ICP4'

SELECT a.account, b.HCS_Code

FROM @TABLEA a

LEFT OUTER JOIN @TABLEB b ON a.account = b.account

WHERE a.HCS_Code <> b.HCS_Code

--I missed this part of the WHERE clause from my

--original answer, was doing two things at once

--and did both poorly

OR b.HCS_Code IS NULL

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 7

Hey,

sorry for the format of the original post. I actually managed to solve it pretty soon after posting. I just had a few moments where I couldn't think. I used the outer join solution. In any case thanks for the quick response.