Most redundant categories in orphan articles

Fork of Categories that have the highest number of orphan articles by ASammour

This query is marked as a draft This query has been published by ASammour.

SQL

use arwiki_p;
 select distinct tl_title, count(tl_from), ll_title
 from templatelinks 
 inner join page on page_title = tl_title
 inner join langlinks on ll_from = tl_from
 where page_namespace=10 
 and ll_lang = "ar"
 and tl_title like "%presidents%"
 and page_is_redirect = 0
 and page_id not in (select ll_from from langlinks where ll_lang = "ar" and page_id = ll_from)
 and tl_from in (select ll_from from langlinks where ll_lang = "ar" and tl_from = ll_from)
 and ll_title in (select arwiki_p.page.page_title from arwiki_p.categorylinks inner join arwiki_p.page on arwiki_p.page.page_id = arwiki_p.categorylinks.cl_from where arwiki_p.categorylinks.cl_to = "جميع_المقالات_اليتيمة")
 group by tl_title 
 order by count(tl_from) desc
 limit 500;

SQL

Checking query status...