Broken category redirects on Commons

Fork of Empty categories on Commons by Achim55

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

SQL

#USE stop;
USE commonswiki_p;
SELECT CONCAT ('[[Category:', REPLACE (page_title,'_',' '), ']]') AS touchlist
FROM page
INNER JOIN templatelinks ON tl_from = page_id AND tl_namespace = 10 AND tl_title = 'Category_redirect'
INNER JOIN pagelinks ON pl_from = page_id AND pl_from_namespace = 14 AND pl_namespace = 14
WHERE page_namespace = 14
AND (SELECT COUNT(*) FROM pagelinks pl2 WHERE pl2.pl_from = page_id AND pl2.pl_from_namespace = 14 AND pl2.pl_namespace = 14) = 1
AND NOT EXISTS (SELECT 1 FROM page p2 WHERE p2.page_namespace = 14 AND p2.page_title = pl_title)
LIMIT 250;

SQL

Checking query status...