• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 41
  • Last Modified:

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,
0
csePixelated
Asked:
csePixelated
  • 3
  • 3
1 Solution
 
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
 
awking00Commented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now