Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Retro
.
Toggle Highlighting
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" ) AND cl_from = primary_page_id ) /* 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
.
Checking query status...