SELECT DISTINCT CONCAT('[[', CASE t.page_namespace

                               WHEN 10 THEN 'Template:'

                               WHEN 14 THEN ':Category:'

                               WHEN 828 THEN 'Module:'

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

                             END, t.page_title, ']]') AS 'redir_to',

                CONCAT('[[', CASE r.page_namespace

                               WHEN 0 THEN ':'

                               WHEN 2 THEN 'User:'

                               WHEN 4 THEN 'Wikipedia:'

                               WHEN 10 THEN 'Template:'

                               WHEN 12 THEN 'Help:'

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

                             END, r.page_title, ']]') AS 'redir_from'

FROM page t

LEFT JOIN redirect ON t.page_namespace = rd_namespace

                      AND t.page_title = rd_title

LEFT JOIN page r ON r.page_id = rd_from

JOIN categorylinks ON t.page_id = cl_from

WHERE cl_to IN ('Redirect_templates'

                ,'Redirect_templates_for_WikiProjects'

                ,'Templates_for_ISO_redirects'

                ,'Templates_for_printworthy_redirects'

                ,'Templates_for_redirects_based_on_association'

                ,'Templates_for_redirects_based_on_association_or_synonymy'

                ,'Templates_for_redirects_based_on_naming_conventions'

                ,'Templates_for_redirects_based_on_page_history_or_potential'

                ,'Templates_for_redirects_based_on_printworthiness'

                ,'Templates_for_redirects_based_on_synonymy'

                ,'Templates_for_redirects_from_a_former_name'

                ,'Templates_for_redirects_from_a_longer_name'

                ,'Templates_for_redirects_from_a_modification_of_the_target_name'

                ,'Templates_for_redirects_from_a_shorter_name'

                ,'Templates_for_redirects_from_alternative_names'

                ,'Templates_for_redirects_from_different_spellings'

                ,'Templates_for_redirects_from_specific_kinds_of_articles'

                ,'Templates_for_redirects_from_subtopic_of_target_page'

                ,'Templates_for_redirects_from_technical_shortcuts'

                ,'Templates_for_redirects_involving_diacritics_or_language_change'

                ,'Templates_for_redirects_relating_to_Swiss_municipalities'

                ,'Templates_for_redirects_relating_to_business'

                ,'Templates_for_redirects_relating_to_fiction'

                ,'Templates_for_redirects_relating_to_geography'

                ,'Templates_for_redirects_relating_to_highways'

                ,'Templates_for_redirects_relating_to_human_names'

                ,'Templates_for_redirects_relating_to_language'

                ,'Templates_for_redirects_relating_to_music'

                ,'Templates_for_redirects_relating_to_people'

                ,'Templates_for_redirects_relating_to_science'

                ,'Templates_for_redirects_relating_to_transportation'

                ,'Templates_for_redirects_relating_to_years'

                ,'Templates_for_redirects_resulting_from_merges_or_moves'

                ,'Templates_for_redirects_to_articles'

                ,'Templates_for_redirects_to_disambiguation_pages'

                ,'Templates_for_redirects_to_lists'

                ,'Templates_for_redirects_to_specific_namespaces'

                ,'Templates_for_redirects_to_specific_types_of_articles'

                ,'Templates_for_redirects_to_subtopic_of_source_page'

                ,'Templates_for_redirects_to_templates'

                ,'Templates_for_redirects_with_possibilities'

                ,'Templates_for_redirects_without_possibilities'

                ,'Templates_for_soft_redirects'

                ,'Templates_for_unprintworthy_redirects'

                ,'WikiProject_Comics_redirect_templates'

                ,'WikiProject_Middle-earth_redirect_templates'

               )

ORDER BY redir_to, redir_from;