USE zhwiki_p;

SELECT CONCAT("[[Template:",template.page_title,"]]") AS `title` FROM (

  SELECT * FROM page WHERE page_title LIKE "CGroup/%" AND page_namespace = 10

) AS template

LEFT JOIN (

  SELECT * FROM page WHERE page_title LIKE "CGroup/%" AND page_namespace = 828

) AS module ON template.page_title = module.page_title

LEFT JOIN (

  SELECT * FROM redirect WHERE rd_title LIKE "CGroup/%"

) AS redirect ON redirect.rd_from = template.page_id

WHERE module.page_title IS NOT NULL

  AND redirect.rd_title IS NULL

  AND template.page_title != "CGroup/preload"

ORDER BY template.page_title

;

​