SELECT

  CONCAT(

    CASE page.page_namespace

      WHEN 0 THEN ''

      WHEN 1 THEN 'Talk'

      WHEN 2 THEN 'User'

      WHEN 3 THEN 'User talk'

      WHEN 4 THEN 'Wikipedia'

      WHEN 5 THEN 'Wikipedia talk'

      WHEN 6 THEN 'File'

      WHEN 7 THEN 'File 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 100 THEN 'Portal'

      WHEN 101 THEN 'Portal talk'

      WHEN 118 THEN 'Draft'

      WHEN 119 THEN 'Draft talk'

      WHEN 126 THEN 'MOS'

      WHEN 127 THEN 'MOS talk'

      WHEN 710 THEN 'TimedText'

      WHEN 711 THEN 'TimedText talk'

      WHEN 828 THEN 'Module'

      WHEN 829 THEN 'Module talk'

      ELSE page.page_namespace END,

    ':', page.page_title

  ) AS page

FROM redirect

INNER JOIN page ON page.page_id = redirect.rd_from

WHERE

  page.page_namespace = redirect.rd_namespace

  AND page.page_title = redirect.rd_title

  AND page.page_namespace != 2 AND page.page_namespace != 3

  AND page.page_title != 'Example_of_a_self_redirect'

ORDER BY page_namespace, page_title;