Fork of Redirects from Foo to The Foo by Certes
This query is marked as a draft This query has been published by Certes.

SQL

AخA
 
SELECT Ps.page_title AS singular, Pp.page_title AS plural,
(SELECT COUNT(*) FROM pagelinks WHERE pl_namespace = 0 AND pl_title = Ps.page_title AND pl_from_namespace = 0) AS slinks,
(SELECT COUNT(*) FROM pagelinks WHERE pl_namespace = 0 AND pl_title = Pp.page_title AND pl_from_namespace = 0) AS plinks
FROM page Ps
JOIN page Pp ON Pp.page_title = CASE /* Make some English-specific assumptions about how plurals are formed */
                                WHEN Ps.page_title REGEXP "([cs]h|[cosxz])$" THEN CONCAT(Ps.page_title, "es") /* box → boxes */
                                WHEN Ps.page_title REGEXP "[^aeiou]y$" THEN REGEXP_REPLACE(Ps.page_title, "y$", "ies") /* fly → flies */
                                ELSE CONCAT(Ps.page_title, "s") END
             AND Pp.page_namespace = 0
WHERE Ps.page_namespace = 0 AND Ps.page_title REGEXP "^\\p{Lu}\\p{Ll}+$" /* Single words only */
AND Ps.page_is_redirect = 0 /* Not sure about this restriction */
AND NOT EXISTS (SELECT 1 FROM templatelinks WHERE tl_from = Ps.page_id AND tl_target_id IN
                (SELECT lt_id FROM linktarget WHERE lt_namespace = 10 AND lt_title IN ("Infobox_newspaper", "Infobox_person", "Infobox_settlement")))
AND NOT EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = Ps.page_id AND cl_to IN
                ("Coordinates_on_Wikidata", "Given_names", "Surnames"))
/* AND NOT EXISTS (SELECT 1 FROM redirect WHERE rd_from = Ps.page_id AND rd_namespace = 0 AND rd_title = Pp.page_title) /* Foo redirects to Foos */
AND NOT EXISTS (SELECT 1 FROM redirect WHERE rd_from = Pp.page_id AND rd_namespace = 0 AND rd_title = Ps.page_title) /* Foos redirects to Foo */
/* Needed only if singular can be a redirect: AND NOT EXISTS (SELECT 1 FROM redirect Rs
                JOIN redirect Rp ON Rp.rd_from = Pp.page_id AND Rp.rd_namespace = 0 AND Rp.rd_title = Rs.rd_title
                WHERE Rs.rd_from = Ps.page_id AND Rs.rd_namespace = 0) /* Foo and Foos redirect to same target */
AND NOT EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = Ps.page_id AND cl_to = "All_disambiguation_pages")
AND NOT EXISTS (SELECT 1 FROM categorylinks WHERE cl_from = Pp.page_id AND cl_to = "All_disambiguation_pages")
/* Needed only if singular can be a redirect: AND NOT EXISTS (SELECT 1 FROM redirect
                JOIN page Pr ON Pr.page_title = rd_title AND Pr.page_namespace = 0
                JOIN categorylinks ON cl_from = Pr.page_id AND cl_to = "All_disambiguation_pages"
                WHERE rd_from = Ps.page_id AND rd_namespace = 0) /* Foo redirects to a dab */
AND NOT EXISTS (SELECT 1 FROM redirect
                JOIN page Pr ON Pr.page_title = rd_title AND Pr.page_namespace = 0
                JOIN categorylinks ON cl_from = Pr.page_id AND cl_to = "All_disambiguation_pages"
                WHERE rd_from = Pp.page_id AND rd_namespace = 0) /* Plural redirects to a dab */
HAVING slinks >= 500 AND plinks > 0
ORDER BY singular
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
All SQL code is licensed under CC0 License.

Checking query status...