SELECT CONCAT('[[', CASE page_namespace

                      WHEN 0 THEN ''                      WHEN 1 THEN 'Talk:'

                      WHEN 2 THEN 'User:'                 WHEN 3 THEN 'User talk:'

                      WHEN 4 THEN 'Meta:'                 WHEN 5 THEN 'Meta talk:'

                      WHEN 8 THEN 'MediaWiki:'            WHEN 9 THEN 'MediaWiki talk:'

                      WHEN 10 THEN 'Template:'            WHEN 11 THEN 'Template talk:'

                      WHEN 12 THEN 'Help:'                WHEN 13 THEN 'Help talk:'

                      WHEN 14 THEN ':Category:'           WHEN 15 THEN 'Category talk:'

                      WHEN 200 THEN 'Grants:'             WHEN 201 THEN 'Grants talk:'

                      WHEN 202 THEN 'Research:'           WHEN 203 THEN 'Research talk:'

                      WHEN 206 THEN 'Iberocoop:'          WHEN 207 THEN 'Iberocoop talk:'

                      WHEN 828 THEN 'Module:'             WHEN 829 THEN 'Module talk:'

                      WHEN 866 THEN 'CNBanner:'           WHEN 867 THEN 'CNBanner talk:'

                      WHEN 1198 THEN 'Translations:'      WHEN 1199 THEN 'Translations talk:'

                      ELSE CONCAT('{{ns:', page_namespace, '}}:')

                    END, page_title,']]'),

       SUBSTRING_INDEX(page_title, '/', -1) AS lang_in_title,

       page_lang

FROM page

WHERE page_title LIKE '%/%'

  AND page_lang IS NULL

  AND SUBSTRING_INDEX(page_title, '/', -1) IN

    (SELECT DISTINCT page_lang

     FROM page

     WHERE page_lang != 'en')

  AND page_namespace != 1198;