USE fawiki_p;

SELECT CONCAT ('# [[',S.page_title,']] > [[:',ll_lang,':',ll_title,']]'), T.page_title

FROM page S INNER JOIN langlinks ON S.page_id = ll_from JOIN redirect ON rd_from = S.page_id JOIN page T ON T.page_title = rd_title

WHERE S.page_namespace = 0

AND S.page_is_redirect = 1

AND T.page_namespace = 0

AND T.page_is_redirect = 0

#AND ll_lang ='en'

AND NOT EXISTS (

  SELECT null

  FROM langlinks

  WHERE ll_from = T.page_id

)

GROUP BY S.page_title

ORDER BY COUNT(ll_from) DESC;