WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4,

  'Wikipedia:'), (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'),

  (11, 'Template talk:'), (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101,

  'Portal talk:'), (118, 'Draft:'), (119, 'Draft talk:'), (126, 'MOS:'), (127, 'MOS talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'),

  (828, 'Module:'), (829, 'Module talk:')),

rcats(rcat_title) AS

(

  SELECT page_title

  FROM page

  JOIN categorylinks ON cl_from = page_id AND cl_to = 'All_redirect_categories'

  WHERE page_namespace = 14

)

SELECT CONCAT('{{!r|', REPLACE(predir.page_title, '_', ' '), '}}') AS 'redirect from',

       CONCAT('[[', COALESCE(ns_s, '?:'), REPLACE(rd_title, '_', ' '), ']]') AS 'redirect to',

       CONCAT('[[:Category:', REPLACE(clt.cl_to, '_', ' '), ']]') AS 'tgt in cat'

FROM redirect

LEFT JOIN ns ON ns_n = rd_namespace

JOIN page AS predir ON predir.page_id = rd_from

LEFT JOIN categorylinks AS clr ON clr.cl_from = predir.page_id AND clr.cl_to IN (SELECT rcat_title FROM rcats)

JOIN page AS ptgt ON ptgt.page_namespace = rd_namespace AND ptgt.page_title = rd_title

JOIN categorylinks AS clt ON clt.cl_from = ptgt.page_id

                            AND clt.cl_to IN ('Academic_publishing_companies', 'Non-profit_academic_publishers', 'Open_access_publishers')

WHERE predir.page_namespace = 0

  AND clr.cl_from IS NULL

LIMIT 10000;