select GROUP_CONCAT(p1.page_title SEPARATOR ', '), p2.page_title from page p1

join redirect on p1.page_id = rd_from and rd_namespace = 0 -- and rd_fragment != ''

join page p2 on p2.page_title = rd_title and p2.page_namespace = 0

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

and p1.page_title in (

  select page_title from page where page_namespace = 1 and page_is_redirect = 0 and page_len > 10

  /*

  and page_id in (

    select tl_from from templatelinks where tl_target_id in (

      select lt_id from linktarget where lt_title in ('WikiProject_Disambiguation') and lt_namespace = 10

    )

  )

  */

  /*

  and page_id in (

    select tl_from from templatelinks where tl_target_id in (

      select lt_id from linktarget where lt_title in ('Talk_page_of_redirect') and lt_namespace = 10

    )

  )

  */

  and page_id not in (

    select tl_from from templatelinks where tl_target_id not in (

      select lt_id from linktarget where lt_title in ('WikiProject_Articles_for_creation') and lt_namespace = 10

    )

  )

)

and p2.page_title not in (

  select page_title from page where page_namespace = 1 -- and page_is_redirect = 0

)

​

and p2.page_id in (

  select cl_from from categorylinks

  where cl_to in ("All_disambiguation_pages"/*, "All_set_index_articles"*/) and cl_type = 'page'

)

​

group by p2.page_title

order by count(p1.page_title) desc