Sub-cats of Commons cats linked from a page

Fork of Commons sub-cats with no Wikidata sitelinks by Jheald

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

SQL

use commonswiki_p;
SELECT DISTINCT p2.page_title, GROUP_CONCAT(p3.page_title SEPARATOR ' / ')
FROM page AS p1
JOIN pagelinks AS pl1 ON pl1.pl_from=p1.page_id AND pl1.pl_from_namespace=p1.page_namespace AND pl1.pl_namespace=14
JOIN page AS p2 ON pl1.pl_from=p2.page_id AND p2.page_namespace=14 AND p2.page_is_redirect=0
LEFT JOIN categorylinks AS c2 ON c2.cl_to = p2.page_title
LEFT JOIN page AS p3 ON c2.cl_from=p3.page_id AND p3.page_namespace=14 AND p3.page_is_redirect=0
#JOIN categorylinks AS c_up ON c_up.cl_from=p2.page_id


WHERE p1.page_title='Jheald/temp4' AND p1.page_namespace=2
GROUP BY p2.page_title;
# LIMIT 500;

SQL

Checking query status...