SQL Server – How to Create a Mapping Table

sql serversql-server-2012

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'
            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?

Best Answer

Create intermediate derived tables of your main and mapping tables and then join these to compare and use a CASE statement to check the matching rows.

CREATE TABLE #MAP_CITY_A (CODE TINYINT, CITY VARCHAR(10), MEANING VARCHAR(10))
INSERT #MAP_CITY_A (CODE,CITY,MEANING)
VALUES (90, 'Paris', 'Active'),(85, 'Paris', 'Not_Active'),(60, 'Sydney', 'Active'),(75, 'Sydney', 'Not_Active')

CREATE TABLE #MAP_CITY_B (CODE VARCHAR(3), CITY VARCHAR(10), MEANING VARCHAR(10))
INSERT #MAP_CITY_B (CODE,CITY,MEANING)
VALUES ('IN', 'PA', 'Active'),('OUT', 'PA', 'Not_Active'),('IN', 'SY', 'Active'),('OUT', 'SY', 'Not_Active')


SELECT a.ID,
       a.CITY,
       CASE 
          WHEN a.MEANING = b.MEANING THEN 'MATCH'
          ELSE 'MISMATCH'
       END as RESULT
FROM
(
    SELECT CITY_A.ID, CITY_A.CITY, #MAP_CITY_A.MEANING 
    FROM CITY_A 
    JOIN #MAP_CITY_A ON CITY_A.CODE = #MAP_CITY_A.CODE
         AND CITY_A.CITY = #MAP_CITY_A.CITY
) a
JOIN 
(
    SELECT CITY_B.ID, #MAP_CITY_B.MEANING 
    FROM CITY_B 
    JOIN #MAP_CITY_B ON CITY_B.CODE = #MAP_CITY_B.CODE
         AND CITY_B.CITY = #MAP_CITY_B.CITY
) b  
ON a.ID = b.ID
ORDER BY a.ID
Related Question