Interlanguage links for pages linked from specific page

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

Interlanguage links to ko: from pages linked from [[User:Ca/ko links]] (before I pasted these results over that) and, if those titles exist locally, what they redirect to.

(Long aside: Pasting this back into that subpage was an adventure.  [[Template:No redirect]] (which {{!r}} redirects to) has an expensive parser call, so only 500 work per view.  So not only did I have to do the usual irritating cleanup (replacing &#124; with | and, for null results, the *entirely* unhelpful "None" that Quarry plops in only when downloading as a wikitable with &nbsp;), I had to paste them in about 450 entries at a time.  Which turned into 225 for most of the page, since I didn't notice until near the end I was copying 450 lines instead of 450 entries, and each entry has 2 lines in the table markup.

In retrospect, this would have been better off:
 1. being pasted into a different subpage, to make rerunning the query less fraught;
 2. implementing {{no redirect}} within this query, both checking for the page's existence and formatting the output correctly, to bypass the parser function and to be able to better highlight any such titles that exist but aren't redirects (there are three); and
 3. using SDZeroBot and {{database report}} to run the query instead of Quarry, to make repetition by the end-user easier.)

For [[WP:VPT#Wikidata help]] circa 16 February 2025.  Results currently in [[User:Ca/ko links]]; initial list of pages from https://petscan.wmcloud.org/?psid=31871180

SQL

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:'), (126, 'MOS:'), (127, 'MOS talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'),
  (828, 'Module:'), (829, 'Module talk:'))

SELECT CONCAT('[[', REPLACE(lt_title, '_', ' '), ']]') AS article,
       CONCAT('[[:ko:', ll_title, ']]') AS kowiki,
       CONCAT('{{!r|', ll_title, '}}') AS 'local link',
       CASE WHEN rd_title IS NOT NULL THEN CONCAT('[[', COALESCE(ns_s, '?:'), REPLACE(rd_title, '_', ' '), ']]') END AS 'redirect to'
FROM page AS src
JOIN pagelinks ON pl_from = src.page_id
JOIN linktarget ON lt_id = pl_target_id
JOIN page AS tgt ON tgt.page_namespace = lt_namespace AND tgt.page_title = lt_title
LEFT JOIN langlinks ON ll_from = tgt.page_id AND ll_lang = 'ko'
LEFT JOIN page AS lcl ON lcl.page_namespace = 0 AND lcl.page_title = REPLACE(ll_title, ' ', '_')
LEFT JOIN redirect ON rd_from = lcl.page_id
LEFT JOIN ns ON ns_n = rd_namespace
WHERE src.page_namespace = 2
  AND src.page_title = 'Ca/ko_links';

​x
 
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:'), (126, 'MOS:'), (127, 'MOS talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'),  (828, 'Module:'), (829, 'Module talk:'))​SELECT CONCAT('[[', REPLACE(lt_title, '_', ' '), ']]') AS article,       CONCAT('[[:ko:', ll_title, ']]') AS kowiki,       CONCAT('{{!r|', ll_title, '}}') AS 'local link',       CASE WHEN rd_title IS NOT NULL THEN CONCAT('[[', COALESCE(ns_s, '?:'), REPLACE(rd_title, '_', ' '), ']]') END AS 'redirect to'FROM page AS srcJOIN pagelinks ON pl_from = src.page_idJOIN linktarget ON lt_id = pl_target_idJOIN page AS tgt ON tgt.page_namespace = lt_namespace AND tgt.page_title = lt_titleLEFT JOIN langlinks ON ll_from = tgt.page_id AND ll_lang = 'ko'LEFT JOIN page AS lcl ON lcl.page_namespace = 0 AND lcl.page_title = REPLACE(ll_title, ' ', '_')LEFT JOIN redirect ON rd_from = lcl.page_idLEFT JOIN ns ON ns_n = rd_namespaceWHERE src.page_namespace = 2  AND src.page_title = 'Ca/ko_links';

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...