most efficient way to compare columns across separate tables

Question

Post reply

most efficient way to compare columns across separate tables

azdeji

SSCrazy

Points: 2535
More actions
May 2, 2022 at 11:39 pm
Go to Answer
#4025712

Hi,
I have no idea how to create this SQL query hence the reason I'm here.
What would be the most efficient way to compare columns across separate tables? If the first rule was to match 5 columns, then the second rule is to match 4, third rule is to match 3 and so on.
I just need an idea of how I would go about doing this.
It like a case when but matching of columns see picture below -
Thanks in advance.

Ed B

Hall of Fame

Points: 3666

May 3, 2022 at 9:56 am

Answer

#4025882

If the tables join on SalesID and SalesID is unique in both tables, I would probably start by joining the tables and creating a match column for each column and populating it with a 1 or a 0. From there you can derive your written summary if needed.

-- Inner join negates need for SalesIDMatch column
SELECT  ...,
        CASE WHEN a.SalesAgent = b.SalesAgent THEN 1 ELSE 0 END AS SalesAgentMatch,
        CASE WHEN a.Region = b.Region THEN 1 ELSE 0 END AS RegionMatch,
        CASE WHEN a.SalesAmount = b.SalesAmount THEN 1 ELSE 0 END AS AmountMatch,
        CASE WHEN a.IsOwner = b.IsOwner THEN 1 ELSE 0 END AS IsOwnerMatch
FROM    schema.Table1 AS a
INNER   JOIN schema.Table2 AS b ON a.SalesID = b.SalesID

-- If you need a left join
SELECT  COLUMNS,
        CASE WHEN a.SalesAgent = b.SalesAgent THEN 1 ELSE 0 END AS SalesAgentMatch,
        CASE WHEN a.Region = b.Region THEN 1 ELSE 0 END AS RegionMatch,
        CASE WHEN a.SalesAmount = b.SalesAmount THEN 1 ELSE 0 END AS AmountMatch,
        CASE WHEN a.IsOwner = b.IsOwner THEN 1 ELSE 0 END AS IsOwnerMatch,
        CASE WHEN b.SalesID IS NULL THEN 0 ELSE 1 END AS SalesID Match
FROM    schema.Table1 AS a
LEFT    OUTER JOIN schema.Table2 AS b ON a.SalesID = b.SalesID

This may not be the most efficient way, but it's a start, You need to decide if North = north and if you consider two nulls to be a match. If you do consider nulls a match, then you may need to ISNULL the columns before comparing them. It two nulls are not a match, what about two empty strings?

May 4, 2022 at 10:44 pm

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

What is the primary key on these tables? And, are you really using 2019? If not, what version?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)