Untitled query #46057

SQL

use enwiki_p;
select DISTINCT CONCAT('Category:' , p1.page_title), replace (p1.page_title, "_coaches", "") , lle.ll_title
from page p1 , langlinks lle
where p1.page_namespace = 14
and p1.page_is_redirect = 0
and p1.page_title like "%_coaches"
and p1.page_id not in (select cl_from
    from  categorylinks
    where cl_to in ( "Disambiguation_categories" , "Wikipedia_soft_redirected_categories" ) )
and p1.page_id not in (select ll_from from langlinks where ll_from = p1.page_id and ll_lang = "ar")
and ((select p2.page_id from page p2
    where p2.page_is_redirect = 0 and p2.page_namespace in (0 , 14) and p2.page_title = replace(p1.page_title, "_coaches", "")
    )  = lle.ll_from )
and lle.ll_lang = "ar"
group by p1.page_title

 limit 40
SQL

Checking query status...
