USE trwiki_p;

SET @KAT=replace("Türkçe Vikipedi üzerinde resmi olan ama Vikiveride resmi olmayan maddeler"," ","_"); -- Local image but no image on Wikidata 

SET @KAT2=replace("Bozuk dosya bağlantıları içeren sayfalar"," ","_"); 

​

select concat("[[",replace(page_title,"_"," "),"]]") as ad

, concat("tr.wikipedia.org/wiki?curid=",cl_from) as url

, il_to as commons_resimlerinden_bir_tanesi

, count(*) as maddedeki_resim_sayisi

from categorylinks join page on page_id = cl_from join imagelinks on il_from = cl_from

left join image on img_name = il_to

where cl_type = "page" and cl_to = @KAT 

and img_user is null

and cl_from not in (select distinct(ill.il_from) from imagelinks ill join image imm on ill.il_to = imm.img_name)

and cl_from not in (select cll.cl_from from categorylinks cll where cl_to = @KAT2)

and il_to not rlike "Flag"

and il_to not rlike "Commons-logo"

and il_to not rlike "Locator_Dot"

and il_to not rlike "ocation_map"

group by cl_from

order by count(*), page_latest

​

;