USE enwiki_p;

SELECT CONCAT('File:', p.page_title), min(particle.page_title), count(*) FROM page p

LEFT JOIN categorylinks c1 ON p.page_id = c1.cl_from AND c1.cl_to = "All_non-free_media"

LEFT JOIN redirect on rd_from = p.page_id

LEFT JOIN imagelinks i ON p.page_title = i.il_to AND (i.il_from_namespace = 0)

INNER JOIN page particle ON particle.page_id = il_from

INNER JOIN image ON img_name = p.page_title

INNER JOIN categorylinks ca1 ON particle.page_id = ca1.cl_from

LEFT JOIN page pca1 ON pca1.page_title = ca1.cl_to

LEFT JOIN categorylinks ca2 ON pca1.page_id = ca2.cl_from

LEFT JOIN page pca2 ON pca2.page_title = ca2.cl_to

LEFT JOIN categorylinks ca3 ON pca2.page_id = ca3.cl_from

LEFT JOIN page pca3 ON pca3.page_title = ca3.cl_to

LEFT JOIN categorylinks ca4 ON pca3.page_id = ca4.cl_from

WHERE p.page_namespace = 6

AND c1.cl_to IS NULL -- Exclude fair use - other processes will handle those

AND rd_title IS NULL -- Exclude redirects

AND (COALESCE(ca1.cl_to, '') = 'Virginia_Tech' OR COALESCE(ca2.cl_to, '') = 'Virginia_Tech' OR COALESCE(ca3.cl_to, '') = 'Virginia_Tech' OR COALESCE(ca4.cl_to, '') = 'Virginia_Tech')

GROUP BY p.page_title having count(*) < 5

ORDER BY p.page_id DESC