Files in Individual_painting_categories without wikidata

Fork of Paintings_without_Wikidata_item in Individual_painting_categories2 by Jarekt

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

SQL

use commonswiki_p;
SELECT concat("File:",p2.page_title)
FROM 
(
  
SELECT c2.cl_to, count(p1.page_id) as num
FROM page p1
JOIN categorylinks c1 ON p1.page_id=c1.cl_from AND c1.cl_to='Paintings_without_Wikidata_item'
JOIN categorylinks c2 ON p1.page_id=c2.cl_from 
WHERE p1.page_namespace = 6 -- file 
AND c2.cl_to IN (SELECT p3.page_title -- categories in "Individual_painting_categories" category
                 FROM page p3
                 INNER JOIN categorylinks c3 on p3.page_id = c3.cl_from AND c3.cl_to = "Individual_painting_categories"
                 WHERE p3.page_namespace = 14 -- category 
                 )

group by c2.cl_to
having num>5
) as X
JOIN categorylinks c4 ON c4.cl_to=X.cl_to
JOIN page p2 ON p2.page_id=c4.cl_from and p2.page_namespace = 6 -- file

SQL

Checking query status...