WITH CommonPages AS (

    -- Your existing query to find common pages

    SELECT

        common_page_title,

        common_page_namespace

    FROM (

        SELECT

            page_title AS common_page_title,

            page_namespace AS common_page_namespace

        FROM

            page

        WHERE

            CONCAT(page_namespace, ':', page_title) IN (

                SELECT

                    CONCAT(ar_namespace, ':', ar_title)

                FROM

                    archive

                WHERE

                    ar_title IS NOT NULL AND ar_namespace IS NOT NULL

            )

        UNION

        SELECT

            ar_title AS common_page_title,

            ar_namespace AS common_page_namespace

        FROM

            archive

        WHERE

            CONCAT(ar_namespace, ':', ar_title) IN (

                SELECT

                    CONCAT(page_namespace, ':', page_title)

                FROM

                    page

                WHERE

                    page_title IS NOT NULL AND page_namespace IS NOT NULL

            )

    ) AS common_pages

)

​

-- Use the CommonPages CTE to append namespace names

SELECT

    CONCAT(

        CASE

            WHEN common_page_namespace = 0 THEN ''

            WHEN common_page_namespace = 1 THEN 'Разговор:'

            WHEN common_page_namespace = 2 THEN 'Корисник:'

            WHEN common_page_namespace = 3 THEN 'Разговор с корисником:'

            WHEN common_page_namespace = 4 THEN 'Википедија:'

            WHEN common_page_namespace = 5 THEN 'Разговор о Википедији:'

            WHEN common_page_namespace = 6 THEN 'Датотека:'

            WHEN common_page_namespace = 7 THEN 'Разговор о датотеци:'

            WHEN common_page_namespace = 8 THEN 'Медијавики:'

            WHEN common_page_namespace = 9 THEN 'Разговор о Медијавикију:'

            WHEN common_page_namespace = 10 THEN 'Шаблон:'

            WHEN common_page_namespace = 11 THEN 'Разговор о шаблону:'

            WHEN common_page_namespace = 12 THEN 'Помоћ:'

            WHEN common_page_namespace = 13 THEN 'Разговор о помоћи:'

            WHEN common_page_namespace = 14 THEN 'Категорија:'

            WHEN common_page_namespace = 15 THEN 'Разговор о категорији:'

            WHEN common_page_namespace = 100 THEN 'Портал:'

            WHEN common_page_namespace = 101 THEN 'Разговор о порталу:'

            WHEN common_page_namespace = 118 THEN 'Нацрт:'

            WHEN common_page_namespace = 119 THEN 'Разговор о нацрту:'

            WHEN common_page_namespace = 710 THEN 'TimedText:'

            WHEN common_page_namespace = 711 THEN 'Разговор о TimedText:'

            WHEN common_page_namespace = 828 THEN 'Модул:'

            WHEN common_page_namespace = 829 THEN 'Разговор о модулу:'

            ELSE ''

        END,

        common_page_title

    ) AS modified_page_title

FROM

    CommonPages;

​