Categories only in Hidden/Tracking/CatAutoTOC tree

Fork of Categories only in Hidden_categories and Tracking_categories by Cryptic

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

Categories that are in both [[Category:Hidden categories]] and [[Category:Tracking categories]] and in no other categories, except perhaps [[Category:CatAutoTOC tracking categories]] or one of its immediate subcategories.

For [[WP:VPT#PetScan or DB query for categories exclusively in Category:Hidden categories and Category:Tracking categories]] circa 6 October 2022.

SQL

SET @basecat='CatAutoTOC_tracking_categories';
SET max_recursive_iterations=1;

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,
       EXISTS (SELECT subcat FROM deepcat WHERE subcat = page_title) AS `in CatAutoTOC subcat`
FROM page
JOIN categorylinks AS hc ON hc.cl_from = page_id AND hc.cl_to = 'Hidden_categories'
JOIN categorylinks AS tc ON tc.cl_from = page_id AND tc.cl_to = 'Tracking_categories'
LEFT JOIN categorylinks AS oc
  ON oc.cl_from = page_id
  AND oc.cl_to NOT IN ('Hidden_categories', 'Tracking_categories')
  AND oc.cl_to NOT IN (SELECT subcat FROM deepcat)
WHERE page_namespace = 14
  AND oc.cl_to IS NULL;

SQL

Checking query status...