wikt: pages in category tree w/0 incoming mainspace links

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

Pages in the main namespace, with no incoming links from the main namespace, that are in either [[Category:Non-lemma forms by language]] or one of its descendant subcategories.

max_recursive_iterations=6 is enough for the entire tree, totaling 3625 categories.

See query 66388 for a version that shows entries with no incoming links from any namespace (and with debugging detritus left in the query, which elicited duplicate rows in the result).

For [[w:WP:RAQ#Wiktionary query]] circa 3 August 2022.

SQL

SET @basecat='Non-lemma_forms_by_language';
SET max_recursive_iterations=6;

WITH RECURSIVE deepcat (subcat) AS
(
  SELECT @basecat

  UNION

  SELECT page_title
  FROM categorylinks
  JOIN page ON page_id = cl_from AND page_namespace = 14
  JOIN deepcat ON cl_to = subcat
)
SELECT page_title, cl_to
FROM page
JOIN categorylinks ON cl_from = page_id
JOIN deepcat ON cl_to = subcat
LEFT JOIN pagelinks ON pl_namespace = page_namespace AND pl_title = page_title AND pl_from_namespace = 0
WHERE page_namespace = 0
  AND pl_from IS NULL;

​x
 
SET @basecat='Non-lemma_forms_by_language';SET max_recursive_iterations=6;​WITH RECURSIVE deepcat (subcat) AS(  SELECT @basecat​  UNION​  SELECT page_title  FROM categorylinks  JOIN page ON page_id = cl_from AND page_namespace = 14  JOIN deepcat ON cl_to = subcat)SELECT page_title, cl_toFROM pageJOIN categorylinks ON cl_from = page_idJOIN deepcat ON cl_to = subcatLEFT JOIN pagelinks ON pl_namespace = page_namespace AND pl_title = page_title AND pl_from_namespace = 0WHERE page_namespace = 0  AND pl_from IS NULL;​

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