Query to compare values between multiple columns

Question

Post reply

Query to compare values between multiple columns

rjjh78

SSCrazy

Points: 2787
More actions
October 13, 2016 at 11:52 am

#322107

To all:
I am looking to create a “Change Query” between a Test and Prod table. I want to Compare Name1 and Name2 on server #1 to see if they match either column Name1 OR Name2 on server #2.
Below, is my sample code. In my example, I am expecting only on one row to return which is: (LocID, s1Name1, s1Name2, s2Name1, s2Name2) (5, Joey, Robby, Joey, Will) however, I cannot seem to figure out how to do this comparison.
CREATE TABLE #Server1 (LocID int, Name1 varchar(25), Name2 varchar(25))
INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (1, 'Marky', 'Ricky')
INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (2, 'Danny', 'Terry')
INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (3, 'Mikey', 'Davey')
INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (4, 'Timmy', 'Tommy')
INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (5, 'Joey', 'Robby')
-- SELECT * FROM #SERVER1
CREATE TABLE #Server2 (LocID int, Name1 varchar(25), Name2 varchar(25))
INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (1, 'Marky', 'Ricky')
INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (2, 'Terry', 'Danny')
INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (3, 'Mikey', 'Davey')
INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (4, 'Tommy', 'Timmy')
INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (5, 'Joey', 'Will')
-- SELECT * FROM #SERVER2
What is the best way to query this data?
SELECT
s1.LocID,
s1.Name1 as s1Name1,
s1.Name2 as s1Name2,
s2.Name1 as s2Name1,
s2.Name2 as s2Name2
FROM #SERVER1 s1 JOIN #SERVER2 s2 ON s2.LocID = s1.LocID
Any help is greatly appreciated!

Viewing 4 posts - 1 through 4 (of 4 total)

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

Y.B. SSChampion Points: 11652 More actions · Answer 1

I believe this gets you what you are looking for...

SELECT

s1.LocID,

s1.Name1 as s1Name1,

s1.Name2 as s1Name2,

s2.Name1 as s2Name1,

s2.Name2 as s2Name2

FROM #Server1 s1

JOIN #Server2 s2 ON s2.LocID = s1.LocID

WHERE (s1.Name1 <> s2.Name1 AND s1.Name1 <> s2.Name2) OR (s1.Name2 <> s2.Name1 AND s1.Name2 <> s2.Name2)

SELECT quote FROM brain WHERE original = 1
0 rows returned

rjjh78 SSCrazy Points: 2787 More actions · Answer 2

That's it thank you .... perhaps I was overthinking it! 🙂

drew.allen SSC Guru Points: 77014 More actions · Answer 3

Y.B. (10/13/2016)
I believe this gets you what you are looking for...
SELECT
s1.LocID,
s1.Name1 as s1Name1,
s1.Name2 as s1Name2,
s2.Name1 as s2Name1,
s2.Name2 as s2Name2
FROM #Server1 s1
JOIN #Server2 s2 ON s2.LocID = s1.LocID
WHERE (s1.Name1 <> s2.Name1 AND s1.Name1 <> s2.Name2) OR (s1.Name2 <> s2.Name1 AND s1.Name2 <> s2.Name2)

I think it's close, but not exactly right. Consider the following slightly different set of data where one of the records has the same value for both names.

CREATE TABLE #Server1 (LocID int, Name1 varchar(25), Name2 varchar(25))

INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (1, 'Marky', 'Ricky')

INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (2, 'Danny', 'Terry')

INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (3, 'Mikey', 'Davey')

INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (4, 'Timmy', 'Timmy')

INSERT INTO #Server1 (LocID, Name1, Name2) VALUES (5, 'Joey', 'Robby')

-- SELECT * FROM #SERVER1

CREATE TABLE #Server2 (LocID int, Name1 varchar(25), Name2 varchar(25))

INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (1, 'Marky', 'Ricky')

INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (2, 'Terry', 'Danny')

INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (3, 'Mikey', 'Davey')

INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (4, 'Tommy', 'Timmy')

INSERT INTO #Server2 (LocID, Name1, Name2) VALUES (5, 'Joey', 'Will')

-- SELECT * FROM #SERVER2

I think this altered record should also appear in the output, but it doesn't. Here's a slight modification to the query which will produce the correct results.