SQL
x
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 1
FROM deepcat
JOIN categorylinks ON cl_to = subcat
WHERE cl_from = page_id) 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;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.