USE enwiki_p;

SELECT

  page_title,

  /*

  The code for generating the following two columns was copied from: 

  https://quarry.wmflabs.org/query/36294

  (Another query by me.)

  */

  CASE page_is_redirect

    WHEN 1 THEN "(R)"

  END AS R,

  (

    SELECT GROUP_CONCAT(page2_title SEPARATOR " ")

    FROM redirect

    JOIN (

      SELECT

        page_id AS page2_id,

        CASE page_namespace

          WHEN 10 THEN CONCAT("Template:", page_title)

          WHEN 11 THEN CONCAT("Template talk:", page_title)

        END AS page2_title

      FROM page

    ) AS page2 ON page2_id = rd_from

    WHERE rd_namespace = page_namespace AND rd_title = page_title

  ) AS "List of redirects"

FROM page

LEFT JOIN page_restrictions ON pr_page = page_id

WHERE

  page_namespace = 10

  AND (

    page_title RLIKE "^[A-Za-z]{2,3}\_icon"

    OR page_title LIKE "Zh-%icon"

    OR page_title LIKE "Seto_icon%"

    OR page_title LIKE "Zh-hans%"

  )

  AND page_title NOT LIKE "Air_icon%"

  AND page_title NOT LIKE "Bio_icon%"

  AND page_title NOT LIKE "Bus_icon%"

  AND page_title NOT LIKE "DYK_icon_link%"

  AND page_title NOT LIKE "GA_icon%"

  AND page_title NOT LIKE "GAR_icon%"

  AND page_title NOT LIKE "HSU_icon%"

  AND page_title NOT LIKE "MTS_icon%"

  AND page_title NOT LIKE "WiR_icon%"

  AND page_title NOT LIKE "Top_icon%"

  /* Check for pages with Template Editor protection so I can request notices be added to them on WP:AN */

  /* AND pr_level = "templateeditor" */

GROUP BY page_id

ORDER BY page_is_redirect

/*For some reason, joining templatelinks was too slow:

​

INNER JOIN templatelinks ON tl_from = page_id

​

...

​

WHERE tl_namespace = 10

  AND tl_title = "Link_language"*/