Possibly orphaned talk subpages (parent redirects)

Fork of Possibly orphaned talk subpages (parent missing) by Certes

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

SQL

SELECT Ps.page_namespace AS CSns, Ps.page_title AS CurrentSubpage,
       Ps.page_namespace AS SSns, CONCAT(Pp.page_title,REGEXP_SUBSTR(Ps.page_title, "\/.*")) AS SuggestedSubpage,
       rd_namespace AS RFns, Pp.page_title AS RedirectFrom,
       Ps.page_namespace AS RTns, rd_title AS RedirectTo
FROM page Ps
JOIN page Pp ON Pp.page_namespace>2 AND Pp.page_namespace%2=1 AND Pp.page_title=REGEXP_SUBSTR(Ps.page_title, ".*?(?=\/)")
JOIN redirect ON rd_from=Pp.page_id
WHERE Ps.page_namespace=Pp.page_namespace AND Ps.page_title LIKE "%/%" AND Ps.page_is_redirect=0
AND NOT EXISTS (SELECT 1 FROM page WHERE page_namespace=Pp.page_namespace-1 AND page_title=Ps.page_title)
ORDER BY Ps.page_title

SQL

Checking query status...