USE trwiki_p;

SET @kat = replace("Tüm adil kullanım dosyaları"," ","_");

​

-- Adil kullanim iddiali dosyaya maddelerden baglanti var ama adinda telif gecmeyen bir maddeye dosyadan baglanti yok

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

from page pd join imagelinks on il_to = pd.page_title 

join page pm on pm.page_id = il_from

where pd.page_namespace = 6 and pd.page_id in (select cl_from from categorylinks where cl_type="file" and cl_to = @kat)

and il_from_namespace = 0 and pm.page_namespace =0

and pd.page_id not in (select pl_from from pagelinks where pl_from_namespace = 6 and pl_namespace = 0 and pl_title not rlike "telif")

limit 10;

​

-- Adil kullanim iddiali dosyanin yonlendirme olan olmayan madde baglantilari arasinda dosyaya baglanan maddelerden en az biri yok

select concat("tr.wikipedia.org/wiki?curid=",pd.page_id) as url, pm.page_title as pmad

from page pd join imagelinks on il_to = pd.page_title 

join page pm on pm.page_id = il_from

join pagelinks pl on pl.pl_from = pd.page_id

where pd.page_namespace = 6 and pd.page_id in (select cl_from from categorylinks where cl_type="file" and cl_to = @kat)

and il_from_namespace = 0 and pm.page_namespace =0

and pl.pl_from_namespace = 6 and pl.pl_namespace = 0 and pl_title not rlike "telif"

and pm.page_id not in (select plm.page_id from page plm where plm.page_namespace =0 and plm.page_is_redirect = 0 

                       and plm.page_title = pl.pl_title )

and pm.page_title not in (select rd_title from page plm2 join redirect on rd_from = plm2.page_id

                       where plm2.page_namespace =0 and plm2.page_is_redirect = 1 

                       and plm2.page_title = pl.pl_title

                       and rd_namespace = 0

                      )

​

;

​

​