Link to home
Start Free TrialLog in
Avatar of csePixelated
csePixelatedFlag for United States of America

asked on

compair for rows that do not exist between SQL tables

i am trying to compare the same database on 2 different servers. I am trying to only display results where one of 6 fields do not match between the tables in question or if an a row does exist on one while it does on the other. it seems to work so far, the problem is that it does not include results for AccountID discrepencies. as in the AccountID (and thus the row) does not exist on one table when it does on the other, how do i handle this?
SELECT  A.AccountID AS ACCID,
        B.AcctLineCode AS OLC,
        B.AcctNum AS OAN,
        A.AcctLineCode as NLC,
        A.AcctNum AS NAN,
        B.Name AS ONA,
        A.Name as NNA,
        B.UserField1 AS O1,
        B.UserField2 AS O2,
        B.UserField3 AS O3,
        A.UserField1 AS N1,
        A.UserField2 AS N2,
        A.UserField3 AS N3
FROM    [CDISERV1].[SUBSCRIBER].[dbo].[Subscriber Data] A
        LEFT OUTER JOIN [CDISERV2].[SUBSCRIBER].[dbo].[Subscriber Data] B ON A.AccountID = B.AccountID
WHERE A.AccountID != B.AccountID 
OR A.AcctLineCode != B.AcctLineCode
OR A.AcctNum != B.AcctNum
OR A.Name != B.Name
OR A.UserField1 != B.UserField1
OR A.UserField2 != B.UserField2
OR A.UserField3 != B.UserField3

Open in new window


any help in getting this figured out would be greatly appreciated...

B.Account, B.AcctLineCode, B.AcctNum, B.Name, B.UserField1, B.UserField2, B.UserField3,
01, aa, 1000, bob dole, na, na, yes,
02, aa, 1400, betty dole, na, na, yes,
03, aa, 1050, bob 2, na, na, yes,
04, aa, 4500, bob 3, na, na, yes,
05, aa, 1060, nobody, na, na, yes,
06, aa, 7000, Thomas Coleville, na, na, yes,
07, aa, 8000, the soup shop, na, na,no,  
09, aa, 1234, Bruce banner, na, Hulk, na,
10, aa, 1555, steve b, na, na, yes,
11, aa, 1001, boby b, na, na, yes,

A.Account, A.AcctLineCode, A.AcctNum, A.Name, A.UserField1, A.UserField2, A.UserField3,
01, aa, 1000, bob dole, na, na, yes,
03, aa, 1050, bob 2, na, na, yes,
04, aa, 4500, bob 3, na, na, yes,
05, aa, 1060, nobody, na, na, yes,
06, aa, 7000, Thomas Coleville, na, na, yes,
07, aa, 8000, the soup shop, na, na,no,
08, aa, 1230, nathen m, na, na, yes,
09, aa, 1234, Bruce banner, na, NA, na,

I'm looking for my results from comparing the above to look like ( dashes not necessary )
-, 08, -, aa, -, 1230, -, nathen m, -, na, -, na, -, yes,
09, 09, aa, aa, 1234, 1234, Bruce banner, Bruce banner, na, na, Hulk, NA, na, na,
10, -, aa, -, 1555, -, steve b, -, na, -, na, -, yes,
11, -, aa, -, 1001, -, boby b, -, na, -, na, -, yes,

instead of what it does...
09, 09, aa, aa, 1234, 1234, Bruce banner, Bruce banner, na, na, Hulk, NA, na, na,
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Just to give you an hint, to begin with, use full outer join. Let me try to put whatever you have given in SSMS and attempt to give query to you in a moment.
ASKER CERTIFIED SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You might try using set operators -
Select AccountID,AcctLineCode,AcctNum,Name,UserField1,UserField2,UserField3
from A
except
Select AccountID,AcctLineCode,AcctNum,Name,UserField1,UserField2,UserField3
from B
This will give you the records for the selected columns in A that don't exist in B. Reversing the order of the select statements will give you the records for the selected columns in B that don't exist in A.
Avatar of csePixelated

ASKER

Nitin Sontakke, i do appreciate the input.
here is what i have now
SELECT  isnull(A.AccountID, B.AccountID) AccountID
  ,case when A.AccountID is not null and B.AccountID is not null then 'Found in both' 
        when A.AccountID is not null and B.AccountID is null then 'Missing in B' 
        when A.AccountID is null and B.AccountID is not null then 'Missing in A' end [Result],

        A.AccountID AS ACCID,
        B.AcctLineCode AS OLC,
        B.AcctNum AS OAN,
        A.AcctLineCode as NLC,
        A.AcctNum AS NAN,
        B.Name AS ONA,
        A.Name as NNA,
        B.UserField1 AS O1,
        B.UserField2 AS O2,
        B.UserField3 AS O3,
        A.UserField1 AS N1,
        A.UserField2 AS N2,
        A.UserField3 AS N3
FROM    [CDISERV1].[SUBSCRIBER].[dbo].[Subscriber Data] A
        FULL OUTER JOIN [CDISERV2].[SUBSCRIBER].[dbo].[Subscriber Data] B ON A.AccountID = B.AccountID
WHERE A.AcctLineCode != B.AcctLineCode
OR A.AcctNum != B.AcctNum
OR A.Name != B.Name
OR A.UserField1 != B.UserField1
OR A.UserField2 != B.UserField2
OR A.UserField3 != B.UserField3

Open in new window

If i want to return results for all rows in both tables I can, the problem I am facing is that i need only to list the differences in these fields in my results, in that, i can't seem to include the " Missing in A " & " Missing in B" results while only getting "Found in both" if it matches my other criteria (the tables i am comparing have over 10 columns i only need to know if a line exists on one that does not on the other or if specific fields don't match.
It needs refining but you gave me my answer, thankyou Nitin Sontakke.
SELECT  isnull(A.AccountID, B.AccountID) AccountID
  ,case when A.AccountID is not null and B.AccountID is not null then 'Found in both' 
        when A.AccountID is not null and B.AccountID is null then 'ADDED' 
        when A.AccountID is null and B.AccountID is not null then 'DEELETED' end [Result],

        A.AccountID AS ACCID,
        B.AcctLineCode AS OLC,
        B.AcctNum AS OAN,
        A.AcctLineCode as NLC,
        A.AcctNum AS NAN,
        B.Name AS ONA,
        A.Name as NNA,
        B.UserField1 AS O1,
        B.UserField2 AS O2,
        B.UserField3 AS O3,
        A.UserField1 AS N1,
        A.UserField2 AS N2,
        A.UserField3 AS N3
FROM    [CDISERV1].[SUBSCRIBER].[dbo].[Subscriber Data] A
        FULL OUTER JOIN [CDISERV2].[SUBSCRIBER].[dbo].[Subscriber Data] B ON A.AccountID = B.AccountID
WHERE (A.AccountID is not null and B.AccountID is null)
OR (A.AccountID is null and B.AccountID is not null)
OR A.AcctLineCode != B.AcctLineCode
OR A.AcctNum != B.AcctNum
OR A.Name != B.Name
OR A.UserField1 != B.UserField1
OR A.UserField2 != B.UserField2
OR A.UserField3 != B.UserField3

Open in new window

Might as well formally close the question, if you are happy with what you have got so far.
Did not realise i had not closed it, ty again Nitin Sontakke.