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
Pols12
.
List all pages which have a page content language different from subpage name which should be the explicit language code. v2
Toggle Highlighting
SQL
SELECT t1.page_lang, t1.page_namespace, t1.page_title, SUBSTRING_INDEX(t1.page_title, '/', -1) AS suffix, -- last subpage name LEFT(t1.page_title, CHAR_LENGTH(t1.page_title) - LOCATE('/', REVERSE(t1.page_title))) AS base, -- page name without suffix revtag.rt_page FROM page AS t1 LEFT JOIN revtag ON t1.page_id = revtag.rt_page LEFT JOIN ( SELECT page_title, rt_page FROM page LEFT JOIN revtag ON page_id = rt_page ) AS t2 ON t1.base = t2.page_title AND t1.page_namespace = t2.page_namespace WHERE revtag.rt_page IS NULL -- not handled by Translate extension (excludes translatable pages and translation units, but not translation pages) AND t2.rt_page IS NULL AND t1.page_title LIKE '%/%' AND ( suffix = 'ab' OR suffix = 'abs' OR suffix = 'ace' OR suffix = 'ady' OR suffix = 'ady-cyrl' OR suffix = 'aeb' OR suffix = 'aeb-arab' OR suffix = 'aeb-latn' OR suffix = 'af' OR suffix = 'ak' OR suffix = 'aln' OR suffix = 'alt' OR suffix = 'am' OR suffix = 'ami' OR suffix = 'an' OR suffix = 'ang' OR suffix = 'ann' OR suffix = 'anp' OR suffix = 'ar' OR suffix = 'arc' OR suffix = 'arn' OR suffix = 'arq' OR suffix = 'ary' OR suffix = 'arz' OR suffix = 'as' OR suffix = 'ase' OR suffix = 'ast' OR suffix = 'atj' OR suffix = 'av' OR suffix = 'avk' OR suffix = 'awa' OR suffix = 'ay' OR suffix = 'az' OR suffix = 'azb' OR suffix = 'ba' OR suffix = 'ban' OR suffix = 'ban-bali' OR suffix = 'bar' OR suffix = 'bbc' OR suffix = 'bbc-latn' OR suffix = 'bcc' OR suffix = 'bci' OR suffix = 'bcl' OR suffix = 'be' OR suffix = 'be-tarask' OR suffix = 'bg' OR suffix = 'bgn' OR suffix = 'bh' OR suffix = 'bho' OR suffix = 'bi' OR suffix = 'bjn' OR suffix = 'blk' OR suffix = 'bm' OR suffix = 'bn' OR suffix = 'bo' OR suffix = 'bpy' OR suffix = 'bqi' OR suffix = 'br' OR suffix = 'brh' OR suffix = 'bs' OR suffix = 'btm' OR suffix = 'bto' OR suffix = 'bug' OR suffix = 'bxr' OR suffix = 'ca' OR suffix = 'cbk-zam' OR suffix = 'cdo' OR suffix = 'ce' OR suffix = 'ceb' OR suffix = 'ch' OR suffix = 'chr' OR suffix = 'chy' OR suffix = 'ckb' OR suffix = 'co' OR suffix = 'cps' OR suffix = 'cr' OR suffix = 'crh' OR suffix = 'crh-cyrl' OR suffix = 'crh-latn' OR suffix = 'cs' OR suffix = 'csb' OR suffix = 'cu' OR suffix = 'cv' OR suffix = 'cy' OR suffix = 'da' OR suffix = 'dag' OR suffix = 'de' OR suffix = 'de-at' OR suffix = 'de-ch' OR suffix = 'de-formal' OR suffix = 'dga' OR suffix = 'din' OR suffix = 'diq' OR suffix = 'dsb' OR suffix = 'dtp' OR suffix = 'dty' OR suffix = 'dv' OR suffix = 'dz' OR suffix = 'ee' OR suffix = 'egl' OR suffix = 'el' OR suffix = 'eml' OR suffix = 'en' OR suffix = 'en-ca' OR suffix = 'en-gb' OR suffix = 'eo' OR suffix = 'es' OR suffix = 'es-formal' OR suffix = 'et' OR suffix = 'eu' OR suffix = 'ext' OR suffix = 'fa' OR suffix = 'fat' OR suffix = 'ff' OR suffix = 'fi' OR suffix = 'fit' OR suffix = 'fj' OR suffix = 'fo' OR suffix = 'fon' OR suffix = 'fr' OR suffix = 'frc' OR suffix = 'frp' OR suffix = 'frr' OR suffix = 'fur' OR suffix = 'fy' OR suffix = 'ga' OR suffix = 'gaa' OR suffix = 'gag' OR suffix = 'gan' OR suffix = 'gan-hans' OR suffix = 'gan-hant' OR suffix = 'gcr' OR suffix = 'gd' OR suffix = 'gl' OR suffix = 'gld' OR suffix = 'glk' OR suffix = 'gn' OR suffix = 'gom' OR suffix = 'gom-deva' OR suffix = 'gom-latn' OR suffix = 'gor' OR suffix = 'got' OR suffix = 'gpe' OR suffix = 'grc' OR suffix = 'gsw' OR suffix = 'gu' OR suffix = 'guc' OR suffix = 'gur' OR suffix = 'guw' OR suffix = 'gv' OR suffix = 'ha' OR suffix = 'hak' OR suffix = 'haw' OR suffix = 'he' OR suffix = 'hi' OR suffix = 'hif' OR suffix = 'hif-latn' OR suffix = 'hil' OR suffix = 'hr' OR suffix = 'hrx' OR suffix = 'hsb' OR suffix = 'hsn' OR suffix = 'ht' OR suffix = 'hu' OR suffix = 'hu-formal' OR suffix = 'hy' OR suffix = 'hyw' OR suffix = 'ia' OR suffix = 'id' OR suffix = 'ie' OR suffix = 'ig' OR suffix = 'igl' OR suffix = 'ii' OR suffix = 'ik' OR suffix = 'ike-cans' OR suffix = 'ike-latn' OR suffix = 'ilo' OR suffix = 'inh' OR suffix = 'io' OR suffix = 'is' OR suffix = 'it' OR suffix = 'iu' OR suffix = 'ja' OR suffix = 'jam' OR suffix = 'jbo' OR suffix = 'jut' OR suffix = 'jv' OR suffix = 'ka' OR suffix = 'kaa' OR suffix = 'kab' OR suffix = 'kbd' OR suffix = 'kbd-cyrl' OR suffix = 'kbp' OR suffix = 'kcg' OR suffix = 'kea' OR suffix = 'kg' OR suffix = 'khw' OR suffix = 'ki' OR suffix = 'kiu' OR suffix = 'kjh' OR suffix = 'kjp' OR suffix = 'kk' OR suffix = 'kk-arab' OR suffix = 'kk-cn' OR suffix = 'kk-cyrl' OR suffix = 'kk-kz' OR suffix = 'kk-latn' OR suffix = 'kk-tr' OR suffix = 'kl' OR suffix = 'km' OR suffix = 'kn' OR suffix = 'ko' OR suffix = 'ko-kp' OR suffix = 'koi' OR suffix = 'kr' OR suffix = 'krc' OR suffix = 'kri' OR suffix = 'krj' OR suffix = 'krl' OR suffix = 'ks' OR suffix = 'ks-arab' OR suffix = 'ks-deva' OR suffix = 'ksh' OR suffix = 'ksw' OR suffix = 'ku' OR suffix = 'ku-arab' OR suffix = 'ku-latn' OR suffix = 'kum' OR suffix = 'kus' OR suffix = 'kv' OR suffix = 'kw' OR suffix = 'ky' OR suffix = 'la' OR suffix = 'lad' OR suffix = 'lb' OR suffix = 'lbe' OR suffix = 'lez' OR suffix = 'lfn' OR suffix = 'lg' OR suffix = 'li' OR suffix = 'lij' OR suffix = 'liv' OR suffix = 'lki' OR suffix = 'lld' OR suffix = 'lmo' OR suffix = 'ln' OR suffix = 'lo' OR suffix = 'loz' OR suffix = 'lrc' OR suffix = 'lt' OR suffix = 'ltg' OR suffix = 'lus' OR suffix = 'luz' OR suffix = 'lv' OR suffix = 'lzh' OR suffix = 'lzz' OR suffix = 'mad' OR suffix = 'mag' OR suffix = 'mai' OR suffix = 'map-bms' OR suffix = 'mdf' OR suffix = 'mg' OR suffix = 'mhr' OR suffix = 'mi' OR suffix = 'min' OR suffix = 'mk' OR suffix = 'ml' OR suffix = 'mn' OR suffix = 'mni' OR suffix = 'mnw' OR suffix = 'mo' OR suffix = 'mos' OR suffix = 'mr' OR suffix = 'mrh' OR suffix = 'mrj' OR suffix = 'ms' OR suffix = 'ms-arab' OR suffix = 'mt' OR suffix = 'mwl' OR suffix = 'my' OR suffix = 'myv' OR suffix = 'mzn' OR suffix = 'na' OR suffix = 'nah' OR suffix = 'nan' OR suffix = 'nap' OR suffix = 'nb' OR suffix = 'nds' OR suffix = 'nds-nl' OR suffix = 'ne' OR suffix = 'new' OR suffix = 'nia' OR suffix = 'niu' OR suffix = 'nl' OR suffix = 'nl-informal' OR suffix = 'nmz' OR suffix = 'nn' OR suffix = 'nod' OR suffix = 'nog' OR suffix = 'nov' OR suffix = 'nqo' OR suffix = 'nrm' OR suffix = 'nso' OR suffix = 'nv' OR suffix = 'ny' OR suffix = 'nyn' OR suffix = 'nys' OR suffix = 'oc' OR suffix = 'ojb' OR suffix = 'olo' OR suffix = 'om' OR suffix = 'or' OR suffix = 'os' OR suffix = 'pa' OR suffix = 'pag' OR suffix = 'pam' OR suffix = 'pap' OR suffix = 'pcd' OR suffix = 'pcm' OR suffix = 'pdc' OR suffix = 'pdt' OR suffix = 'pfl' OR suffix = 'pi' OR suffix = 'pih' OR suffix = 'pl' OR suffix = 'pms' OR suffix = 'pnb' OR suffix = 'pnt' OR suffix = 'prg' OR suffix = 'ps' OR suffix = 'pt' OR suffix = 'pt-br' OR suffix = 'pwn' OR suffix = 'qu' OR suffix = 'qug' OR suffix = 'rgn' OR suffix = 'rif' OR suffix = 'rki' OR suffix = 'rm' OR suffix = 'rmc' OR suffix = 'rmy' OR suffix = 'rn' OR suffix = 'ro' OR suffix = 'roa-tara' OR suffix = 'rsk' OR suffix = 'ru' OR suffix = 'rue' OR suffix = 'rup' OR suffix = 'ruq' OR suffix = 'ruq-cyrl' OR suffix = 'ruq-latn' OR suffix = 'rw' OR suffix = 'ryu' OR suffix = 'sa' OR suffix = 'sah' OR suffix = 'sat' OR suffix = 'sc' OR suffix = 'scn' OR suffix = 'sco' OR suffix = 'sd' OR suffix = 'sdc' OR suffix = 'sdh' OR suffix = 'se' OR suffix = 'se-fi' OR suffix = 'se-no' OR suffix = 'se-se' OR suffix = 'sei' OR suffix = 'ses' OR suffix = 'sg' OR suffix = 'sgs' OR suffix = 'sh' OR suffix = 'sh-cyrl' OR suffix = 'sh-latn' OR suffix = 'shi' OR suffix = 'shn' OR suffix = 'shy' OR suffix = 'shy-latn' OR suffix = 'si' OR suffix = 'sjd' OR suffix = 'sje' OR suffix = 'sk' OR suffix = 'skr' OR suffix = 'skr-arab' OR suffix = 'sl' OR suffix = 'sli' OR suffix = 'sm' OR suffix = 'sma' OR suffix = 'smn' OR suffix = 'sms' OR suffix = 'sn' OR suffix = 'so' OR suffix = 'sq' OR suffix = 'sr' OR suffix = 'sr-ec' OR suffix = 'sr-el' OR suffix = 'srn' OR suffix = 'sro' OR suffix = 'ss' OR suffix = 'st' OR suffix = 'stq' OR suffix = 'sty' OR suffix = 'su' OR suffix = 'sv' OR suffix = 'sw' OR suffix = 'syl' OR suffix = 'szl' OR suffix = 'szy' OR suffix = 'ta' OR suffix = 'tay' OR suffix = 'tcy' OR suffix = 'tdd' OR suffix = 'te' OR suffix = 'tet' OR suffix = 'tg' OR suffix = 'tg-cyrl' OR suffix = 'tg-latn' OR suffix = 'th' OR suffix = 'ti' OR suffix = 'tk' OR suffix = 'tl' OR suffix = 'tly' OR suffix = 'tn' OR suffix = 'to' OR suffix = 'tok' OR suffix = 'tpi' OR suffix = 'tr' OR suffix = 'tru' OR suffix = 'trv' OR suffix = 'ts' OR suffix = 'tt' OR suffix = 'tt-cyrl' OR suffix = 'tt-latn' OR suffix = 'tum' OR suffix = 'tw' OR suffix = 'ty' OR suffix = 'tyv' OR suffix = 'tzm' OR suffix = 'udm' OR suffix = 'ug' OR suffix = 'ug-arab' OR suffix = 'ug-latn' OR suffix = 'uk' OR suffix = 'ur' OR suffix = 'uz' OR suffix = 've' OR suffix = 'vec' OR suffix = 'vep' OR suffix = 'vi' OR suffix = 'vls' OR suffix = 'vmf' OR suffix = 'vmw' OR suffix = 'vo' OR suffix = 'vot' OR suffix = 'vro' OR suffix = 'wa' OR suffix = 'wal' OR suffix = 'war' OR suffix = 'wls' OR suffix = 'wo' OR suffix = 'wuu' OR suffix = 'xal' OR suffix = 'xh' OR suffix = 'xmf' OR suffix = 'xsy' OR suffix = 'yi' OR suffix = 'yo' OR suffix = 'yrl' OR suffix = 'yue' OR suffix = 'za' OR suffix = 'zea' OR suffix = 'zgh' OR suffix = 'zh' OR suffix = 'zh-cn' OR suffix = 'zh-hans' OR suffix = 'zh-hant' OR suffix = 'zh-hk' OR suffix = 'zh-mo' OR suffix = 'zh-my' OR suffix = 'zh-sg' OR suffix = 'zh-tw' OR suffix = 'zu' ) AND t1.page_title NOT LIKE CONCAT('%/', IFNULL(t1.page_lang, 'en')) LIMIT 1
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...