zhwiki qualified titles with shared base name lacking a (disambiguation) page

Fork of Qualified titles with shared base name lacking a (disambiguation) page by Certes

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

SQL

SELECT REGEXP_SUBSTR(page_title, "(.*?)(?=_\\(消歧.\\)|,_|$)") AS basename,
 SUM(1 - page_is_redirect) pages, GROUP_CONCAT(page_title ORDER BY page_title SEPARATOR'|') AS titles
FROM page Pq
WHERE page_namespace = 0 /* AND page_title LIKE "D%"  AND page_title NOT REGEXP "_\\((season|series|\\d{4}(.\\d{4})?\\))|\\)." */
AND (NOT EXISTS (SELECT 1 FROM page Pb /* basename links to qualified name */
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0 AND pl_title = Pq.page_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, ",_", 1) AND Pb.page_title != Pq.page_title)
 AND NOT EXISTS (SELECT 1 FROM page Pb /* qualified name is a redirect, and basename links to its target */
                 JOIN redirect ON rd_from = Pq.page_id AND rd_namespace = 0
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0 AND pl_title = rd_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, ",_", 1) AND Pb.page_title != Pq.page_title
                 AND Pq.page_is_redirect = 1)
 AND NOT EXISTS (SELECT 1 FROM page Pb /* basename links to redirect to qualified name */
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0
                 JOIN page Pr ON Pr.page_namespace = 0 AND Pr.page_title = pl_title
                 JOIN redirect ON rd_from = Pr.page_id  AND rd_namespace = 0 AND rd_title = Pq.page_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, ",_", 1) AND Pb.page_title != Pq.page_title)
 AND NOT EXISTS (SELECT 1 FROM page Pb /* qualified name is a redirect, and basename links to another redirect to its target */
                 JOIN redirect Rq ON Rq.rd_from = Pq.page_id AND Rq.rd_namespace = 0
                 JOIN pagelinks ON pl_from = Pb.page_id AND pl_namespace = 0
                 JOIN page Pr ON Pr.page_namespace = 0 AND Pr.page_title = pl_title
                 JOIN redirect Rb ON Rb.rd_from = Pr.page_id AND Rb.rd_namespace = 0 AND Rb.rd_title = Rq.rd_title
                 WHERE Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, ",_", 1) AND Pb.page_title != Pq.page_title
                 AND Pq.page_is_redirect = 1)
 AND NOT EXISTS (SELECT 1 FROM redirect /* qualified name redirects to basename */
                 JOIN page Pb ON Pb.page_namespace = 0 AND Pb.page_title = rd_title AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, ",_", 1)
                 WHERE rd_from = Pq.page_id AND rd_namespace = 0)
 AND NOT EXISTS (SELECT 1 FROM linktarget /* basename links to some other dab */
                 JOIN page Pb ON Pb.page_namespace = 0 AND Pb.page_title = SUBSTRING_INDEX(Pq.page_title, ",_", 1)
                 JOIN templatelinks ON tl_from = Pb.page_id AND tl_target_id = lt_id
                 WHERE lt_namespace = 10 AND lt_title = "Other_uses")
 OR page_title LIKE "%消歧%")
GROUP BY basename
HAVING pages >= 3
AND MAX(CASE WHEN page_title LIKE "%消歧%" THEN 1 ELSE 0 END) = 0
AND MAX(CASE WHEN page_title = basename AND page_is_redirect = 1 THEN 1 ELSE 0 END) = 0
ORDER BY basename

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...