wikt: pages in category tree w/0 incoming links

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

Pages in the main namespace, with no incoming links whatsoever (from any 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 66402 for a version that shows only entries with no incoming links from the main namespace and a cleaner query.  In particular, this one has nearly 3000 redundant rows in the deepcat results, since subcats reachable by more than one path in the tree are listed once for each path.  Hence, -hakuisuuden appears three times in the results - once in Category:Finnish_non-lemma_forms and twice in Category:Finnish_noun_forms (which is itself in Category:Finnish_non-lemma_forms).

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 AS
(
  SELECT CAST(@basecat AS VARCHAR(255)) AS page_title,
         CAST('' AS VARCHAR(255)) AS cl_to

  UNION

  SELECT p2.page_title,
         cl2.cl_to
  FROM categorylinks AS cl2
  JOIN page AS p2 ON p2.page_id = cl2.cl_from AND p2.page_namespace = 14
  JOIN deepcat ON cl2.cl_to = deepcat.page_title
)
SELECT p3.page_title, cl3.cl_to
FROM page AS p3
JOIN categorylinks AS cl3 ON cl_from = p3.page_id
JOIN deepcat ON cl3.cl_to = deepcat.page_title
LEFT JOIN pagelinks ON pl_namespace = p3.page_namespace AND pl_title = p3.page_title
WHERE p3.page_namespace = 0
  AND pl_from IS NULL;

​x
 
SET @basecat='Non-lemma_forms_by_language';SET max_recursive_iterations=6;​WITH RECURSIVE deepcat AS(  SELECT CAST(@basecat AS VARCHAR(255)) AS page_title,         CAST('' AS VARCHAR(255)) AS cl_to​  UNION​  SELECT p2.page_title,         cl2.cl_to  FROM categorylinks AS cl2  JOIN page AS p2 ON p2.page_id = cl2.cl_from AND p2.page_namespace = 14  JOIN deepcat ON cl2.cl_to = deepcat.page_title)SELECT p3.page_title, cl3.cl_toFROM page AS p3JOIN categorylinks AS cl3 ON cl_from = p3.page_idJOIN deepcat ON cl3.cl_to = deepcat.page_titleLEFT JOIN pagelinks ON pl_namespace = p3.page_namespace AND pl_title = p3.page_titleWHERE p3.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...