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,
LVL 1
csePixelatedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
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.
0
Nitin SontakkeDeveloperCommented:
And here is the script to demonstrate. Feel free to alter to suit your requirement. Concept is all that matters.

declare @tableA table
(
   [Account] integer not null
  ,[AcctLineCode] varchar(10) not null
  ,[AcctNum] integer not null
  ,[Name] varchar(50) not null
  ,[UserField1] varchar(20)
  ,[UserField2] varchar(20)
  ,[UserField3] varchar(20)
)

insert into @tableA values 
 (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') 

declare @tableB table
(
   [Account] integer not null
  ,[AcctLineCode] varchar(10) not null
  ,[AcctNum] integer not null
  ,[Name] varchar(50) not null
  ,[UserField1] varchar(20)
  ,[UserField2] varchar(20)
  ,[UserField3] varchar(20)
)

insert into @tableB values 
 (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')

select isnull(a.[Account], b.[Account]) [Account]
  ,case when a.[Account] is not null and b.[Account] is not null then 'Found in both' 
        when a.[Account] is not null and b.[Account] is null then 'Missing in B' 
        when a.[Account] is null and b.[Account] is not null then 'Missing in A' end [Result]
  ,*
from @tableA a
full outer join @tableB b on a.[Account] = b.[Account]
order by 1

Open in new window


And here is the screen shot of the output:

29091923.png
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

csePixelatedAuthor Commented:
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.
0
csePixelatedAuthor Commented:
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

0
Nitin SontakkeDeveloperCommented:
Might as well formally close the question, if you are happy with what you have got so far.
0
csePixelatedAuthor Commented:
Did not realise i had not closed it, ty again Nitin Sontakke.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.