-- See [[Special:Diff/904016069]]

SELECT *

FROM (

SELECT

  pl_from,

  -- pl_from_namespace does not appear to be consistently reliable;

  -- it might be better to select from the page table and join the pagelinks table to it.

  CASE

    -- This fails on pages missing from the page table (presumably because they were deleted).

    WHEN pl_from_namespace != page_namespace THEN CONCAT(pl_from_namespace, ' vs. ', page_namespace)

    ELSE pl_from_namespace

  END AS 'NS',

  page_title,

  COUNT(pl_title) AS num_L,

  pl_namespace AS 'L NS',

  GROUP_CONCAT(pl_title SEPARATOR ' ') AS 'L titles',

  CASE

    WHEN EXISTS(

      SELECT 1

      FROM templatelinks

      WHERE

        tl_from = pl_from

        AND tl_title = 'Under_construction'

    ) THEN '(C)'

  END AS C

FROM pagelinks

LEFT JOIN page ON page_id = pl_from

WHERE

  pl_from_namespace = 0

  AND pl_namespace = 2

  -- AND pl_namespace != 0

GROUP BY pl_from -- An unfortunate necessity to avoid excessive templatelinks rows

ORDER BY page_title

) AS t1

WHERE NOT (num_L = 1 AND C != '(C)')