How to create a mapping table? 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?
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