sql_query.sql

SELECT table_2.dimension_1, table_2.dimension_2, SUM(table_1.measure_1) AS measure_1, SUM(table_2.measure_2) AS measure_2
FROM (
    SELECT 
        a.dimension_1, 
        COALESCE(a.measure_1, 0) AS measure_1, 
        m.correct_dimension_2 AS dimension_2
    FROM table_a AS a
    INNER JOIN (
        SELECT DISTINCT dimension_1, correct_dimension_2 
        FROM table_map 
        WHERE dimension_1 IS NOT NULL AND correct_dimension_2 IS NOT NULL
    ) AS m ON a.dimension_1 = m.dimension_1
) AS table_1
INNER JOIN (
    SELECT 
        b.dimension_1, 
        COALESCE(b.measure_2, 0) AS measure_2, 
        m.correct_dimension_2 AS dimension_2
    FROM table_b AS b
    INNER JOIN (
        SELECT DISTINCT dimension_1, correct_dimension_2 
        FROM table_map 
        WHERE dimension_1 IS NOT NULL AND correct_dimension_2 IS NOT NULL
    ) AS m ON b.dimension_1 = m.dimension_1
) AS table_2
ON table_1.dimension_1 = table_2.dimension_1 
AND table_1.dimension_2 = table_2.dimension_2
GROUP BY table_2.dimension_1, table_2.dimension_2;