SELECT CONCAT('https://en.wikipedia.org/wiki/', CASE talk.page_namespace

                WHEN   -2 THEN 'Media:'             WHEN   -1 THEN 'Special:'

                WHEN    0 THEN ''                   WHEN    1 THEN 'Talk:'

                WHEN    2 THEN 'User:'              WHEN    3 THEN 'User talk:'

                WHEN    4 THEN 'Wikipedia:'         WHEN    5 THEN 'Wikipedia talk:'

                WHEN    6 THEN ':File:'             WHEN    7 THEN 'File talk:'

                WHEN    8 THEN 'MediaWiki:'         WHEN    9 THEN 'MediaWiki talk:'

                WHEN   10 THEN 'Template:'          WHEN   11 THEN 'Template talk:'

                WHEN   12 THEN 'Help:'              WHEN   13 THEN 'Help talk:'

                WHEN   14 THEN ':Category:'         WHEN   15 THEN 'Category talk:'

                WHEN  100 THEN 'Portal:'            WHEN  101 THEN 'Portal talk:'

                WHEN  108 THEN 'Book:'              WHEN  109 THEN 'Book talk:'

                WHEN  118 THEN 'Draft:'             WHEN  119 THEN 'Draft talk:'

                WHEN  446 THEN 'Education Program:' WHEN  447 THEN 'Education Program talk:'

                WHEN  710 THEN 'TimedText:'         WHEN  711 THEN 'TimedText talk:'

                WHEN  828 THEN 'Module:'            WHEN  829 THEN 'Module talk:'

                WHEN 2300 THEN 'Gadget:'            WHEN 2301 THEN 'Gadget talk:'

                WHEN 2302 THEN 'Gadget definition:' WHEN 2303 THEN 'Gadget definition talk:'

                ELSE CONCAT('{{ns:', talk.page_namespace, '}}:')

              END,

              talk.page_title) AS url,  

              CONCAT(CASE talk.page_namespace

                WHEN   -2 THEN 'Media:'             WHEN   -1 THEN 'Special:'

                WHEN    0 THEN ''                   WHEN    1 THEN 'Talk:'

                WHEN    2 THEN 'User:'              WHEN    3 THEN 'User talk:'

                WHEN    4 THEN 'Wikipedia:'         WHEN    5 THEN 'Wikipedia talk:'

                WHEN    6 THEN ':File:'             WHEN    7 THEN 'File talk:'

                WHEN    8 THEN 'MediaWiki:'         WHEN    9 THEN 'MediaWiki talk:'

                WHEN   10 THEN 'Template:'          WHEN   11 THEN 'Template talk:'

                WHEN   12 THEN 'Help:'              WHEN   13 THEN 'Help talk:'

                WHEN   14 THEN ':Category:'         WHEN   15 THEN 'Category talk:'

                WHEN  100 THEN 'Portal:'            WHEN  101 THEN 'Portal talk:'

                WHEN  108 THEN 'Book:'              WHEN  109 THEN 'Book talk:'

                WHEN  118 THEN 'Draft:'             WHEN  119 THEN 'Draft talk:'

                WHEN  446 THEN 'Education Program:' WHEN  447 THEN 'Education Program talk:'

                WHEN  710 THEN 'TimedText:'         WHEN  711 THEN 'TimedText talk:'

                WHEN  828 THEN 'Module:'            WHEN  829 THEN 'Module talk:'

                WHEN 2300 THEN 'Gadget:'            WHEN 2301 THEN 'Gadget talk:'

                WHEN 2302 THEN 'Gadget definition:' WHEN 2303 THEN 'Gadget definition talk:'

                ELSE CONCAT('{{ns:', talk.page_namespace, '}}:')

              END,

              REPLACE(talk.page_title, '_', ' ')) AS pagename 

              ,talk.page_id

              -- ,page_is_redirect

              , CASE WHEN talk.page_id IN (SELECT cl_from FROM categorylinks WHERE cl_to = 'Candidates_for_speedy_deletion_as_dependent_on_a_non-existent_page')

                     THEN 'csd_talk'

                     WHEN talk.page_id IN (SELECT cl_from FROM categorylinks WHERE cl_to = 'Candidates_for_speedy_deletion')

                     THEN 'csd'

                ELSE NULL 

                END reason

FROM page talk

-- LEFT JOIN page pagen ON (talk.page_namespace = pagen.page_namespace+1 AND talk.page_title=pagen.page_title)

WHERE 1=1

-- AND pagen.page_id IS NULL

​

-- odd talk pages

AND talk.page_namespace % 2 = 1

​

-- file name space has too many "moved to Commons" files

AND talk.page_namespace NOT IN (7)

​

-- AND talk.page_is_redirect = 0

​

-- not in 3 because user talk pages without user page exist, not in 9 because mediawiki contains language file

AND talk.page_namespace NOT IN (3, 9)

​

-- AND talk.page_title 3like 'O’Brain_Vázquez%' 

​

AND talk.page_title not like '%/%'

​

AND talk.page_title not in (SELECT page_title from page where page_namespace = talk.page_namespace-1)

​

​

AND talk.page_id NOT IN (

  SELECT cl_from FROM categorylinks

  WHERE 2=3

  OR cl_to = 'Wikipedia_orphaned_talk_pages_that_should_not_be_speedily_deleted'

  OR cl_to = 'Non-orphaned_pages_tagged_with_G8-exempt')

​

-- ORDER BY reason asc, talk.page_namespace asc, talk.page_title asc

LIMIT 500