USE fawiki_p;

SELECT concat('[[رده:',REPLACE(cl_to,'_',' '),']]'),COUNT(cl_to)#,CONCAT ('# [[',page_title,']]')

FROM page INNER JOIN categorylinks ON cl_from = page_id

WHERE cl_to NOT LIKE '%ابهام%'

AND page_namespace = 0

AND page_is_redirect = 0

AND page_title IN (

  SELECT page_title

  FROM page

  WHERE page_id IN (

    SELECT pp_page

    FROM page_props

    WHERE pp_propname = 'disambiguation')

)

AND EXISTS (

  SELECT null FROM page AS catpage

  WHERE catpage.page_title = cl_to

  AND catpage.page_namespace = 14

  AND NOT EXISTS (

    SELECT null

    FROM page_props AS catprops

    WHERE catprops.pp_page = catpage.page_id

    AND catprops.pp_propname = 'hiddencat'

  )

)

GROUP BY cl_to

ORDER BY COUNT(cl_to) DESC;