Uncat'd redirs to tgts w/talk transcluding template

Fork of Uncat'd redirs to tgts in named cats by Cryptic

This query is marked as a draft This query has been published by Cryptic.

Redirects from mainspace that have no redirect category (that is, a direct subcat of [[Category:All redirect categories]]) and that target either pages whose corresponding talk page transcludes {{WikiProject Switzerland}}, or directly to talk pages that transclude that template.  (There shouldn't be any of the latter; they'd be CSD R2 candidates.)

For [[WP:RAQ#More redirect queries]] circa 25 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'
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 ttgt ON ttgt.page_namespace = 2*(rd_namespace DIV 2) + 1 AND ttgt.page_title = rd_title
JOIN templatelinks ON tl_from = ttgt.page_id
JOIN linktarget ON lt_id = tl_target_id
WHERE predir.page_namespace = 0
  AND clr.cl_from IS NULL
  AND lt_namespace = 10 AND lt_title = 'WikiProject_Switzerland'
LIMIT 10000;

AخA
 
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'FROM redirectLEFT JOIN ns ON ns_n = rd_namespaceJOIN page AS predir ON predir.page_id = rd_fromLEFT JOIN categorylinks AS clr ON clr.cl_from = predir.page_id AND clr.cl_to IN (SELECT rcat_title FROM rcats)JOIN page AS ttgt ON ttgt.page_namespace = 2*(rd_namespace DIV 2) + 1 AND ttgt.page_title = rd_titleJOIN templatelinks ON tl_from = ttgt.page_idJOIN linktarget ON lt_id = tl_target_idWHERE predir.page_namespace = 0  AND clr.cl_from IS NULL  AND lt_namespace = 10 AND lt_title = 'WikiProject_Switzerland'LIMIT 10000;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...