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