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

SQL

AخA
 
USE enwiki_p;
SELECT
  CASE page_namespace
    WHEN 10 THEN CONCAT("Template:", page_title)
    WHEN 11 THEN CONCAT("Template talk:", page_title)
  END AS "Page title",
  R,
  GROUP_CONCAT(cl_to SEPARATOR "; ") AS "Categories (of primary page)"
FROM
(  
  SELECT
    page_namespace,
    page_title,
    CASE page_is_redirect
      WHEN 1 THEN "(R)"
    END AS R,
    CASE
      WHEN page_is_redirect = 1 THEN rd_target_page_id
      WHEN page_namespace = 11 THEN talk_primary_page_id
      ELSE page_id
    END AS primary_page_id,
    page_id
    /* Ideally, I would utilize some link following code
    so talk pages and redirects can benefit from the categorization of their target pages.
    */
  FROM page
  LEFT JOIN redirect ON page_id = rd_from
  LEFT JOIN (
    SELECT 
      page_id AS rd_target_page_id,
      page_title AS rd_target_page_title
    FROM page
    WHERE page_namespace = 10
  ) AS rd_target_page ON rd_target_page_title = rd_title
  LEFT JOIN (
    SELECT 
      page_id AS talk_primary_page_id,
      page_title AS talk_primary_page_title
    FROM page
    WHERE page_namespace = 10
  ) AS talk_primary_page ON talk_primary_page_title = page_title
  WHERE
    (
      page_namespace = 10
      OR page_namespace = 11
    )
    AND page_title LIKE "%section%"
    AND page_title NOT LIKE "%/doc"
    AND page_title NOT LIKE "%/sandbox"
    AND page_title NOT LIKE "%/testcases"
) AS section_pages
LEFT JOIN categorylinks ON cl_from = primary_page_id
/* WHERE */
  /* AND cl_to != "Templates_for_railway_lines_of_India"
  AND c1_to NOT LIKE "%_Indian_railways_articles"
  AND cl_to NOT LIKE "%_sports_templates" */
GROUP BY page_id
ORDER BY page_title, page_namespace DESC
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...