Jump to content

User:Topbanana/Reports/This article links more than once to another wikipedia/SQL

From Wikipedia, the free encyclopedia

To recreate this report, create a Link Analysis Database then run the SQL below. You may wish to update the list of languages wikipedia is available in - what's below is correct as of 24th September 2004. 

CREATE TABLE lang (
    lang varchar(12) binary NOT NULL,
	PRIMARY KEY( lang )
) ENGINE=MyISAM; 

INSERT INTO lang VALUES ( 'aa' );
INSERT INTO lang VALUES ( 'ab' );
INSERT INTO lang VALUES ( 'af' );
INSERT INTO lang VALUES ( 'als' );
INSERT INTO lang VALUES ( 'am' );
INSERT INTO lang VALUES ( 'an' );
INSERT INTO lang VALUES ( 'ang' );
INSERT INTO lang VALUES ( 'ar' );
INSERT INTO lang VALUES ( 'as' );
INSERT INTO lang VALUES ( 'ast' );
INSERT INTO lang VALUES ( 'ay' );
INSERT INTO lang VALUES ( 'az' );
INSERT INTO lang VALUES ( 'ba' );
INSERT INTO lang VALUES ( 'bal' );
INSERT INTO lang VALUES ( 'ban' );
INSERT INTO lang VALUES ( 'be' );
INSERT INTO lang VALUES ( 'ber' );
INSERT INTO lang VALUES ( 'bg' );
INSERT INTO lang VALUES ( 'bh' );
INSERT INTO lang VALUES ( 'bi' );
INSERT INTO lang VALUES ( 'bn' );
INSERT INTO lang VALUES ( 'bo' );
INSERT INTO lang VALUES ( 'br' );
INSERT INTO lang VALUES ( 'bs' );
INSERT INTO lang VALUES ( 'bug' );
INSERT INTO lang VALUES ( 'ca' );
INSERT INTO lang VALUES ( 'ceb' );
INSERT INTO lang VALUES ( 'ch' );
INSERT INTO lang VALUES ( 'che' );
INSERT INTO lang VALUES ( 'chr' );
INSERT INTO lang VALUES ( 'chv' );
INSERT INTO lang VALUES ( 'co' );
INSERT INTO lang VALUES ( 'cr' );
INSERT INTO lang VALUES ( 'cs' );
INSERT INTO lang VALUES ( 'csb' );
INSERT INTO lang VALUES ( 'cy' );
INSERT INTO lang VALUES ( 'de' );
INSERT INTO lang VALUES ( 'diu' );
INSERT INTO lang VALUES ( 'dk' );
INSERT INTO lang VALUES ( 'dv' );
INSERT INTO lang VALUES ( 'dz' );
INSERT INTO lang VALUES ( 'el' );
INSERT INTO lang VALUES ( 'en)' );
INSERT INTO lang VALUES ( 'eo' );
INSERT INTO lang VALUES ( 'es' );
INSERT INTO lang VALUES ( 'et' );
INSERT INTO lang VALUES ( 'eu' );
INSERT INTO lang VALUES ( 'fa' );
INSERT INTO lang VALUES ( 'fi' );
INSERT INTO lang VALUES ( 'fiu' );
INSERT INTO lang VALUES ( 'fj' );
INSERT INTO lang VALUES ( 'fo' );
INSERT INTO lang VALUES ( 'fr' );
INSERT INTO lang VALUES ( 'fy' );
INSERT INTO lang VALUES ( 'ga' );
INSERT INTO lang VALUES ( 'gay' );
INSERT INTO lang VALUES ( 'gd' );
INSERT INTO lang VALUES ( 'gl' );
INSERT INTO lang VALUES ( 'gn' );
INSERT INTO lang VALUES ( 'got' );
INSERT INTO lang VALUES ( 'gs' );
INSERT INTO lang VALUES ( 'gu' );
INSERT INTO lang VALUES ( 'gv' );
INSERT INTO lang VALUES ( 'ha' );
INSERT INTO lang VALUES ( 'he' );
INSERT INTO lang VALUES ( 'hi' );
INSERT INTO lang VALUES ( 'hr' );
INSERT INTO lang VALUES ( 'hu' );
INSERT INTO lang VALUES ( 'hy' );
INSERT INTO lang VALUES ( 'ia' );
INSERT INTO lang VALUES ( 'iba' );
INSERT INTO lang VALUES ( 'id' );
INSERT INTO lang VALUES ( 'ie' );
INSERT INTO lang VALUES ( 'ik' );
INSERT INTO lang VALUES ( 'io' );
INSERT INTO lang VALUES ( 'is' );
INSERT INTO lang VALUES ( 'it' );
INSERT INTO lang VALUES ( 'iu' );
INSERT INTO lang VALUES ( 'ja' );
INSERT INTO lang VALUES ( 'jbo' );
INSERT INTO lang VALUES ( 'jv' );
INSERT INTO lang VALUES ( 'ka' );
INSERT INTO lang VALUES ( 'kaw' );
INSERT INTO lang VALUES ( 'kir' );
INSERT INTO lang VALUES ( 'kk' );
INSERT INTO lang VALUES ( 'kl' );
INSERT INTO lang VALUES ( 'km' );
INSERT INTO lang VALUES ( 'kn' );
INSERT INTO lang VALUES ( 'ko' );
INSERT INTO lang VALUES ( 'ks' );
INSERT INTO lang VALUES ( 'ku' );
INSERT INTO lang VALUES ( 'kw' );
INSERT INTO lang VALUES ( 'ky' );
INSERT INTO lang VALUES ( 'la' );
INSERT INTO lang VALUES ( 'li' );
INSERT INTO lang VALUES ( 'ln' );
INSERT INTO lang VALUES ( 'lo' );
INSERT INTO lang VALUES ( 'ls' );
INSERT INTO lang VALUES ( 'lt' );
INSERT INTO lang VALUES ( 'lv' );
INSERT INTO lang VALUES ( 'lx' );
INSERT INTO lang VALUES ( 'mad' );
INSERT INTO lang VALUES ( 'mak' );
INSERT INTO lang VALUES ( 'mg' );
INSERT INTO lang VALUES ( 'mi' );
INSERT INTO lang VALUES ( 'min' );
INSERT INTO lang VALUES ( 'mk' );
INSERT INTO lang VALUES ( 'ml' );
INSERT INTO lang VALUES ( 'mn' );
INSERT INTO lang VALUES ( 'mr' );
INSERT INTO lang VALUES ( 'ms' );
INSERT INTO lang VALUES ( 'mt' );
INSERT INTO lang VALUES ( 'my' );
INSERT INTO lang VALUES ( 'na' );
INSERT INTO lang VALUES ( 'nah' );
INSERT INTO lang VALUES ( 'nds' );
INSERT INTO lang VALUES ( 'ne' );
INSERT INTO lang VALUES ( 'ng' );
INSERT INTO lang VALUES ( 'nl' );
INSERT INTO lang VALUES ( 'nn' );
INSERT INTO lang VALUES ( 'no' );
INSERT INTO lang VALUES ( 'oc' );
INSERT INTO lang VALUES ( 'oj' );
INSERT INTO lang VALUES ( 'om' );
INSERT INTO lang VALUES ( 'oo' );
INSERT INTO lang VALUES ( 'or' );
INSERT INTO lang VALUES ( 'pa' );
INSERT INTO lang VALUES ( 'pl' );
INSERT INTO lang VALUES ( 'ps' );
INSERT INTO lang VALUES ( 'pt' );
INSERT INTO lang VALUES ( 'qu' );
INSERT INTO lang VALUES ( 'rm' );
INSERT INTO lang VALUES ( 'rn' );
INSERT INTO lang VALUES ( 'ro' );
INSERT INTO lang VALUES ( 'roa-rup' );
INSERT INTO lang VALUES ( 'ru' );
INSERT INTO lang VALUES ( 'rw' );
INSERT INTO lang VALUES ( 'sa' );
INSERT INTO lang VALUES ( 'sav' );
INSERT INTO lang VALUES ( 'sc' );
INSERT INTO lang VALUES ( 'sd' );
INSERT INTO lang VALUES ( 'sg' );
INSERT INTO lang VALUES ( 'si' );
INSERT INTO lang VALUES ( 'simple' );
INSERT INTO lang VALUES ( 'sk' );
INSERT INTO lang VALUES ( 'sl' );
INSERT INTO lang VALUES ( 'sm' );
INSERT INTO lang VALUES ( 'sn' );
INSERT INTO lang VALUES ( 'so' );
INSERT INTO lang VALUES ( 'son' );
INSERT INTO lang VALUES ( 'sq' );
INSERT INTO lang VALUES ( 'sr' );
INSERT INTO lang VALUES ( 'ss' );
INSERT INTO lang VALUES ( 'st' );
INSERT INTO lang VALUES ( 'su' );
INSERT INTO lang VALUES ( 'sv' );
INSERT INTO lang VALUES ( 'sw' );
INSERT INTO lang VALUES ( 'ta' );
INSERT INTO lang VALUES ( 'te' );
INSERT INTO lang VALUES ( 'tg' );
INSERT INTO lang VALUES ( 'th' );
INSERT INTO lang VALUES ( 'ti' );
INSERT INTO lang VALUES ( 'tk' );
INSERT INTO lang VALUES ( 'tl' );
INSERT INTO lang VALUES ( 'tlh' );
INSERT INTO lang VALUES ( 'tn' );
INSERT INTO lang VALUES ( 'to' );
INSERT INTO lang VALUES ( 'tokipona' );
INSERT INTO lang VALUES ( 'tpi' );
INSERT INTO lang VALUES ( 'tr' );
INSERT INTO lang VALUES ( 'ts' );
INSERT INTO lang VALUES ( 'tt' );
INSERT INTO lang VALUES ( 'tw' );
INSERT INTO lang VALUES ( 'udm' );
INSERT INTO lang VALUES ( 'ug' );
INSERT INTO lang VALUES ( 'uk' );
INSERT INTO lang VALUES ( 'ur' );
INSERT INTO lang VALUES ( 'uz' );
INSERT INTO lang VALUES ( 'vi' );
INSERT INTO lang VALUES ( 'vo' );
INSERT INTO lang VALUES ( 'wa' );
INSERT INTO lang VALUES ( 'wo' );
INSERT INTO lang VALUES ( 'xh' );
INSERT INTO lang VALUES ( 'yi' );
INSERT INTO lang VALUES ( 'yo' );
INSERT INTO lang VALUES ( 'za' );
INSERT INTO lang VALUES ( 'zh-min-nan' );
INSERT INTO lang VALUES ( 'zu' );

CREATE TABLE first_lang_link (
    from_id mediumint(7) unsigned NOT NULL,
    lang varchar(12) binary NOT NULL,	
    pos mediumint(5) unsigned NOT NULL,
	PRIMARY KEY( from_id, lang )
) ENGINE=MyISAM; 

INSERT INTO first_lang_link
SELECT art_id, lang, locate( concat( '[[', lang, ':' ) COLLATE latin1_bin, art_text )
FROM art, lang
WHERE art_is_redirect = 0
AND  locate( concat( '[[', lang, ':' ) COLLATE latin1_bin, art_text ) > 0;


SELECT concat( '*[[', art_title, ']] has multiple links to ', group_concat( lang ) ) 
INTO OUTFILE 'c:\ml.txt'
FROM art JOIN first_lang_link ON art_id = from_id
WHERE locate( concat( '[[', lang, ':' ) COLLATE latin1_bin, art_text, pos + 1) > 0
GROUP BY art_id;
Retrieved from "https://en.wikipedia.org/w/index.php?title=User:Topbanana/Reports/This_article_links_more_than_once_to_another_wikipedia/SQL&oldid=16794047"