This query is marked as a draft This query has been published by Retro.

SQL

AخA
 
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,
  pl_namespace AS 'L NS',
  GROUP_CONCAT(pl_title SEPARATOR ' ') AS 'L titles',
  COUNT(pl_title) AS num_L,
  CASE
    WHEN MAX(CASE WHEN pl_title LIKE '%/sandbox' THEN 1 END) = 1 THEN '(SB)'
    ELSE ''
  END AS SB,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Under_construction'
    ) THEN '(C)'
    ELSE ''
  END AS C,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM categorylinks
      WHERE
        cl_from = pl_from
        AND cl_to = 'Pages_using_Under_construction_with_the_placedby_parameter'
    ) THEN '(PB)'
    ELSE ''
  END AS C_PB,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND (tl_title = 'In use' OR tl_title = 'GOCEinuse')
    ) THEN '(IU)'
    ELSE ''
  END AS IU,
  CASE 
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Db-r2'
    ) THEN '(R2)'
    ELSE ''
  END AS R2,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Copyvio-revdel'
    ) THEN '(RD1)'
    ELSE ''
  END AS RD1,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM templatelinks
      WHERE
        tl_from = pl_from
        AND tl_title = 'Wikipedia_person_user_link'
    ) THEN '(WP)'
    ELSE ''
  END AS WP
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 SB, page_title
) AS t1
WHERE
  (
    (C = '' AND R2 = '' AND RD1 = '' AND WP = '')
    OR num_L > 1 
  )
  AND (C_PB = '' OR num_L > 2)
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...