most efficient way to compare columns across separate tables
-
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.
-
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) -
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.SalesIDThis 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?
-
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