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

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;