USE lvwiki_p;

#SELECT p.page_title

#SELECT CONCAT('[[Attēls:',p.page_title,'|100px]]') as Attels

SELECT CONCAT('Attēls:',p.page_title) as Attels

FROM page p

INNER JOIN categorylinks c ON p.page_id = c.cl_from

LEFT JOIN imagelinks i ON p.page_title = i.il_to

WHERE c.cl_to IN ("Aizsargāti_attēli", "Aizsargāti_logo", "Aizsargātas_pastmarkas",

"Aizsargāti_filmu_plakāti", "Aizsargāti_grāmatu_vāki",

"Aizsargāti_skaņas_faili", "Albuma_vāka_attēli", "Pastmarkas")

AND i.il_from IS NULL

AND p.page_namespace = 6

/*

Paņemt distinct files

​

SELECT CONCAT('#[[:Attēls:',p.page_title,']]') as Attels

Completely unused files:

​

USE lvwiki_p;

SELECT p.page_title FROM page p

INNER JOIN categorylinks c ON p.page_id = c.cl_from

LEFT JOIN imagelinks i ON p.page_title = i.il_to

WHERE c.cl_to = "Aizsargāti_attēli"

AND i.il_from IS NULL

AND p.page_namespace = 6

​

maybe used in other namespaces:

​

USE lvwiki_p;

SELECT p.page_title FROM page p

INNER JOIN categorylinks c ON p.page_id = c.cl_from

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

WHERE c.cl_to = "Aizsargāti_attēli"

AND i.il_from IS NULL

AND p.page_namespace = 6

​

Tagged as non-free:

​

USE lvwiki_p;

SELECT p.page_title FROM page p

INNER JOIN categorylinks c1 ON p.page_id = c1.cl_from

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

LEFT JOIN categorylinks c2 ON p.page_id = c2.cl_from AND c2.cl_to = "All_orphaned_non-free_use_Wikipedia_files"

WHERE c1.cl_to = "All_non-free_media"

AND i.il_from IS NULL

AND c2.cl_from IS NULL

AND p.page_namespace = 6

*/