SELECT DISTINCT

    CONCAT('[[Portal:', p.page_title, ']]') AS 'Existing base page',

    CASE WHEN rd_namespace IS NULL THEN NULL

         WHEN rd_namespace = 100 THEN CONCAT('[[Portal:', rd_title, ']]')

         ELSE CONCAT('[[{{ns:', rd_namespace, '}}:', rd_title, ']]')

    END AS 'Base page redirects to',

    CONCAT('[[Portal:', ar_title, ']]') AS 'Deleted subpage',

    CASE WHEN EXISTS (SELECT 1 FROM page p2

                      WHERE p2.page_namespace = 100

                          AND p2.page_title = ar_title)

         THEN 'Y'

         ELSE 'N'

    END AS 'Deleted subpage exists',

    user_name,

    DATE_FORMAT(log_timestamp, '%Y-%m-%d %H:%i:%s') AS timestamp,

    log_action,

    comment_text

FROM archive

JOIN page p

    ON p.page_namespace = 100 -- Portal:

    AND p.page_title = REGEXP_REPLACE(ar_title, '/.*', '')

LEFT JOIN logging_logindex

    ON log_namespace = 100

    AND log_title = ar_title

    AND log_type = 'delete'

LEFT JOIN user

    ON user_id = log_user

LEFT JOIN comment

    ON comment_id = log_comment_id

LEFT JOIN redirect

    ON rd_from = p.page_id

WHERE ar_namespace = 100

    AND ar_title LIKE '%/%';