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

SQL

AخA
 
WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4,
  'Wikipedia:'), (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'),
  (11,  'Template talk:'), (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101,
  'Portal talk:'), (118, 'Draft:'), (119, 'Draft talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'), (828, 'Module:'), (829,
  'Module talk:'), (2300, 'Gadget:'), (2301, 'Gadget talk:'), (2302, 'Gadget definition:'), (2303, 'Gadget definition talk:')),
stub_lists(sl_id) AS
(
  SELECT page_id
  FROM page
  WHERE page_namespace = 4
    AND page_title IN ('WikiProject_Stub_sorting/Stub_types',
                       'WikiProject_Stub_sorting/Stub_types/Culture',
                       'WikiProject_Stub_sorting/Stub_types/Architecture',
                       'WikiProject_Stub_sorting/Stub_types/Sports',
                       'WikiProject_Stub_sorting/Stub_types/Geography')
)
SELECT CONCAT(tns.ns_s, t.page_title) AS pagename,
       CONCAT(COALESCE(rdns.ns_s, '?:'), rd_title) AS redirects_to
FROM page AS t
LEFT JOIN ns AS tns ON tns.ns_n = t.page_namespace
LEFT JOIN redirect ON rd_from = t.page_id
LEFT JOIN ns AS rdns ON rdns.ns_n = rd_namespace
LEFT JOIN pagelinks AS pl1
       ON pl1.pl_namespace = t.page_namespace AND pl1.pl_title = t.page_title AND pl1.pl_from IN (SELECT sl_id FROM stub_lists)
LEFT JOIN pagelinks AS pl2
       ON pl2.pl_namespace = rd_namespace AND pl2.pl_title = rd_title AND pl2.pl_from IN (SELECT sl_id FROM stub_lists)
WHERE ((t.page_namespace = 10 AND t.page_title LIKE '%-stub')
       OR (t.page_namespace = 14 AND t.page_title LIKE '%stubs'))
      AND pl1.pl_from IS NULL
      AND pl2.pl_from IS NULL;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...