Efficient way for creating mapping

Question

I have 2 tables as below

CITY_A

ID	CODE	CITY
001	90	PARIS
002	90	PARIS
003	85	PARIS
004	60	SYDNEY
005	75	SYDNEY
006	75	SYDNEY

CITY_B

ID	CODE	CITY
001	IN	PA
002	OUT	PA
003	OUT	PA
004	IN	SYD
005	OUT	SYD
006	IN	SYD

The content is same in both tables but the representation is different. I have 2 mapping here for CITY column and CODE column.

Mapping for CITY_A

CODE	CITY	MEANING
90	PARIS	ACTIVE
85	PARIS	NOT_ACTIVE
60	SYDNEY	ACTIVE
75	SYDNEY	NOT_ACTIVE

Mapping for CITY_B

CODE	CITY	MEANING
IN	PA	ACTIVE
OUT	PA	NOT_ACTIVE
IN	SYD	ACTIVE
OUT	SYD	NOT_ACTIVE

Now i have to compare both tables based on the mapping and find the mismatches. So my expected output is

ID	CITY	RESULT
001	PARIS	MATCH
002	PARIS	MISMATCH
003	PARIS	MATCH
004	SYDNEY	MATCH
005	SYDNEY	MATCH
006	SYDNEY	MISMATCH

Currently I'm using CASE with Temp table. It is too big.

SELECT A.ID , CASE WHEN A.CODE = 90 AND A.CITY = 'PARIS' THEN 'ACTIVE' 
                   WHEN A.CODE = 85 AND A.CITY = 'PARIS' THEN 'NOT_ACTIVE' 
               -- FOR OTHER CITIES
               END AS A.CODE_MEANING,
               CITY
INTO #TEMP_CITY_A              
FROM CITY_A A

SELECT B.ID,CASE WHEN B.CODE = 'IN'  AND B.CITY='PA' THEN 'ACTIVE'
                   WHEN B.CODE = 'OUT'  AND B.CITY='PA' THEN 'NOT_ACTIVE' 
              -- FOR OTHER CITIES
            END AS B.CODE_MEANING,
            CASE WHEN B.CITY = 'PA' THEN 'PARIS' 
                 WHEN B.CITY = 'SYD' THEN 'SYDNEY'
             -- FOR OTHER CITIES
            END AS CITY
 INTO #TEMP_CITY_B              
 FROM CITY_B B

SELECT A.ID ,A.CITY, 
     CASE WHEN A.CODE_MEANING!=B.CODE_MEANING THEN 'MISMATCH' ELSE 'MATCH' END AS RESULT
FROM #TEMP_CITY_A A
JOIN #TEMP_CITY_B B ON A.ID = B.ID AND A.CITY = B.CITY

Which is the most efficient way?

Why don't you just JOIN CITY_A with Mapping for CITY_A rather than hardcode the value — Squirrel
There is no Mapping for CITY_A table yet :) @Squirrel. Should I create a table for mapping separately then? Actually I don't have permission to create table. If that is the best way then I will request for it. Only problem is that It will take so much of time for all the approvals — Avinash
if the mapping table is huge with lots of rows, it is best to create as a table. If it is just few rows, you can use a CTE or VALUE Constructor — Squirrel
Not so huge. I have around 25 rows. Can you please show an example with VALUE? @Squirrel — Avinash

Nick Nick · Accepted Answer · 2021-01-21T08:23:55

Here's a possible way to achieve your desired result using CTEs with table value constructors for the mapping tables:

WITH CITY_A_MAP AS (
  SELECT * FROM (
    VALUES(90, 'PARIS', 'Active'),(85, 'PARIS', 'Not_Active'),(60, 'SYDNEY', 'Active'),(75, 'SYDNEY', 'Not_Active')
  ) AS MAP(CODE, CITY, MEANING)
),
CITY_B_MAP AS (
  SELECT * FROM (
    VALUES ('IN', 'PA', 'Active'),('OUT', 'PA', 'Not_Active'),('IN', 'SYD', 'Active'),('OUT', 'SYD', 'Not_Active')
  ) AS MAP(CODE, CITY, MEANING)
)
SELECT A.ID, A.CITY,
       CASE WHEN MA.MEANING = MB.MEANING THEN 'MATCH' ELSE 'MISMATCH' END AS RESULT
FROM CITY_A A
JOIN CITY_A_MAP MA ON MA.CITY = A.CITY AND MA.CODE = A.CODE
JOIN CITY_B B ON B.ID = A.ID
JOIN CITY_B_MAP MB ON MB.CITY = B.CITY AND MB.CODE = B.CODE
ORDER BY A.ID

Output:

ID  CITY    RESULT
1   PARIS   MATCH
2   PARIS   MISMATCH
3   PARIS   MATCH
4   SYDNEY  MATCH
5   SYDNEY  MATCH
6   SYDNEY  MISMATCH

Demo on dbfiddle

Efficient way for creating mapping

1 Answers