Section variants of article message boxes (a.k.a. header templates)

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

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,
    /*
    Overcomplicated redirect/talk page primary page id determination code
    Much trouble could have been saved if I excluded talk pages and redirects entirely,
    but I definitely wanted redirects,
    and thought talk pages might also be useful because they often note previous TfDs.
    I'm fairly sure I've done an exhaustive TfD search of section templates,
    but it never hurts to have a visual reminder to recognize such templates.
    */
    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
  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"
    /* AND page_is_redirect = 0 /* Temporary try, but it timed out the query for some reason. */
) AS section_pages
LEFT JOIN categorylinks ON cl_from = primary_page_id
WHERE
  NOT EXISTS (
    SELECT 1
    FROM categorylinks
    WHERE
      (
        cl_to = "Sports_club_departments_sidebar_templates"
        OR cl_to = "Templates_for_railway_lines_of_India"
        OR cl_to = "Wikipedia_progress_templates"
        OR cl_to = "Scotland_mountains_and_hills_navigational_boxes"
        OR cl_to = "Archival_templates"
        OR cl_to = "Collapse_templates"
        OR cl_to = "Game_log_templates"
        OR cl_to = "Anatomy_templates"
        OR cl_to = "Internal_link_templates"
        OR cl_to = "West_Japan_Railway_Company_templates"
        OR cl_to = "Redirect_templates"
        OR cl_to = "Major_League_Baseball_game_log_templates"
        OR cl_to = "WikiProject_New_York_City_Public_Transportation_templates"
        OR cl_to = "Section_and_anchor_link_formatting_templates"
        OR cl_to = "Philippine_Basketball_Association_templates"
        OR cl_to = "Palakkad_railway_division"
      )
      AND cl_from = primary_page_id
  )
  AND page_title NOT LIKE "Monthly_clean-up_category/%"
  AND page_title NOT LIKE "Did_you_know_nominations/%"
  AND page_title NOT LIKE "Infobox%"
  AND page_title NOT LIKE "Taxonomy/%"
  AND page_title != "ABL_game_log_section"
  AND page_title != "Advanced_base_section_dock_(ABSD)"
  AND page_title != "Afc_section_cleared"
  AND page_title != "BAC-LAC/Header_sections"
  AND page_title != "Big_Ten_Conference_basketball_student_section_navbox"
  AND page_title != "Category_intersection"
  AND page_title != "Cat_main_section"
  AND page_title != "Category_main_section"
  AND page_title != "Clean-up_type_category/messages/pages_with_missing_lead_section"
  AND page_title != "Cleanup/Articles_with_probable_cleanup_sectionon_talk_page"
  AND page_title != "Cleanup_template_documentation_see_also_section_generic_list"
  AND page_title != "Cleanup_template_documentation_see_also_section_generic_list/Archive_1"
  AND page_title != "Collapsible_section_option"
  AND page_title != "Collapsible_sections_option"
  AND page_title != "Cotton_portal_section_heading"
  AND page_title != "Create_talk_page_section"
  AND page_title != "DANFSsection"
  AND page_title != "Dissection"
  AND page_title != "Editnotices/Page/Help_talk:Labeled_section_transclusion"
  AND page_title != "Etymology_section"
  AND page_title != "Female_genitalia_frontal_cross-section_numbered"
  AND page_title != "German_law_section"
  AND page_title != "Guangzhou_Metro_lines/sections"
  AND page_title != "Image_filter_sections"
  AND page_title != "Invitation_to_edit/section"
  AND page_title != "Kabe_Line_suspended_section"
  AND page_title != "Korea_Armed_Forces_Athletic_Corps_sections"
  AND page_title != "Liberty_ship_by_hull_list_section"
  AND page_title != "London_Transport_portal_section_heading"
  AND page_title != "Monthly_clean_up_category/Messages/Article_sections_to_be_split"
  AND page_title != "NBA_game_log_section"
  AND page_title != "NBL_game_log_section"
  AND page_title != "MPBL_game_log_section"
  AND page_title != "Navbox_with_collapsible_sections"
  AND page_title != "New_section"
  AND page_title != "Panellinios_G.S._sections" /* Waiting to be categorized */
  AND page_title != "Policy_section_top"
  AND page_title != "Policy_section"
  AND page_title != "Portalsection"
  AND page_title != "Project_section_header"
  AND page_title != "RD_section"
  AND page_title != "SOIUSA_sections_of_the_Alps"
  AND page_title != "TH_mid_section"
  AND page_title != "Transcluded_sections_for_the_visa_articles"
  AND page_title != "WHA_section_toc"
  AND page_title != "WNBA_game_log_section"
  AND page_title != "WPESC_Newsletter_section_header"
  AND page_title != "WPMILHIST_Newsletter_section_header"
  AND page_title != "WPMILHIST_Newsletter_section_header_2"
  /* cl_to != "Sports_club_departments_sidebar_templates" /* But this doesn't do quite what I want. */
  /* AND cl_to != "Templates_for_railway_lines_of_India"
  AND cl_to NOT LIKE "%_Indian_railways_articles" */
  /* AND page_title NOT LIKE "Monthly_clean-up_category/%"  */
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 Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
