Uncat'd redirs to tgts in named cats

Redirects from mainspace that have no redirect category (that is, a direct subcat of [[Category:All redirect categories]]) and that target pages in any of [[Category:Academic publishing companies]], [[:Category:Non-profit academic publishers]], and/or [[:Category:Open access publishers]].

For [[WP:RAQ#More redirect queries]] circa 16 October 2024.

SQL

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(ptgt.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 ptgt.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;

SQL

Checking query status...