WITH p as (

  SELECT page.page_id, page.page_title 

  FROM page 

  WHERE page.page_namespace =0

) 

, TLDh as (

  SELECT DISTINCT e.el_to_domain_index, SUBSTRING_INDEX(SUBSTRING_INDEX(e.el_to_domain_index,'.',1),'/',-1) as TLD

  FROM externallinks as e INNER JOIN p

  ON p.page_id = e.el_from

  WHERE  LEFT(e.el_to_domain_index,4) ='http' 

    AND SUBSTRING_INDEX(SUBSTRING_INDEX(e.el_to_domain_index,'.',1),'/',-1) NOT IN ('abb',

'abbott',

'abudhabi',

'ac',

'academy',

'aco',

'actor',

'ad',

'adult',

'ae',

'aero',

'af',

'africa',

'ag',

'agency',

'ai',

'akdn',

'al',

'alsace',

'am',

'amsterdam',

'ao',

'app',

'aq',

'ar',

'archi',

'army',

'art',

'as',

'asia',

'at',

'attorney',

'au',

'auction',

'audi',

'audio',

'auto',

'aw',

'ax',

'az',

'ba',

'band',

'bank',

'bar',

'barcelona',

'barclays',

'basketball',

'bayern',

'bb',

'bd',

'be',

'beer',

'berlin',

'best',

'bet',

'bf',

'bg',

'bh',

'bi',

'bible',

'bid',

'bike',

'bio',

'biz',

'bj',

'black',

'blog',

'blue',

'bm',

'bn',

'bnpparibas',

'bo',

'bot',

'boutique',

'br',

'bradesco',

'brussels',

'bs',

'bt',

'business',

'buzz',

'bw',

'by',

'bz',

'bzh',

'ca',

'cab',

'cafe',

'cam',

'camera',

'camp',

'canon',

'capetown',

'capital',

'car',

'cards',

'care',

'careers',

'cars',

'cash',

'cat',

'catering',

'cc',

'cd',

'center',

'cern',

'cf',

'cfa',

'cg',

'ch',

'charity',

'chat',

'church',

'ci',

'city',

'ck',

'cl',

'click',

'clinic',

'cloud',

'club',

'cm',

'cn',

'co',

'coach',

'codes',

'coffee',

'cologne',

'com',

'community',

'company',

'construction',

'consulting',

'cooking',

'cool',

'coop',

'corsica',

'country',

'cr',

'cricket',

'crs',

'cruises',

'cu',

'cv',

'cw',

'cx',

'cy',

'cymru',

'cyou',

'cz',

'dance',

'date',

'de',

'dental',

'design',

'dev',

'dhl',

'diamonds',

'digital',

'direct',

'directory',

'discount',

'dj',

'dk',

'dm',

'do',

'doctor',

'dog',

'domains',

'download',

'dvag',

'dz',

'earth',

'ec',

'eco',

'edeka',

'edu',

'education',

'ee',

'eg',

'email',

'emerck',

'energy',

'engineering',

'er',

'es',

'estate',

'et',

'eu',

'eus',

'events',

'exchange',

'expert',

'exposed',

'express',

'fage',

'fail',

'faith',

'family',

'farm',

'fashion',

'fi',

'film',

'finance',

'fish',

'fishing',

'fit',

'fitness',

'fj',

'fk',

'flights',

'fm',

'fo',

'football',

'foundation',

'fr',

'frl',

'fun',

'fund',

'furniture',

'futbol',

'fyi',

'ga',

'gal',

'gallery',

'game',

'games',

'garden',

'gay',

'gd',

'ge',

'gent',

'gf',

'gg',

'gh',

'gi',

'gift',

'gl',

'glass',

'global',

'gm',

'gmbh',

'gn',

'gold',

'golf',

'goog',

'google',

'gop',

'gov',

'gp',

'gq',

'gr',

'graphics',

'gratis',

'green',

'group',

'gs',

'gt',

'gu',

'guide',

'guitars',

'guru',

'gw',

'gy',

'hamburg',

'haus',

'health',

'help',

'hiv',

'hk',

'hm',

'hn',

'hockey',

'honda',

'horse',

'host',

'hosting',

'house',

'how',

'hr',

'ht',

'hu',

'icu',

'id',

'ie',

'ikano',

'il',

'im',

'imdb',

'immo',

'immobilien',

'in',

'inc',

'industries',

'info',

'ink',

'institute',

'int',

'international',

'invalid',

'investments',

'io',

'iq',

'ir',

'irish',

'is',

'ismaili',

'ist',

'istanbul',

'it',

'je',

'jetzt',

'jm',

'jo',

'jobs',

'joburg',

'jp',

'kaufen',

'ke',

'kg',

'kh',

'ki',

'kim',

'kitchen',

'kiwi',

'km',

'kn',

'koeln',

'komatsu',

'kp',

'kpmg',

'kpn',

'kr',

'krd',

'kw',

'ky',

'kz',

'la',

'land',

'lat',

'law',

'lawyer',

'lb',

'lc',

'leclerc',

'legal',

'lgbt',

'li',

'lidl',

'life',

'lighting',

'limited',

'link',

'live',

'lk',

'local',

'lol',

'london',

'love',

'lr',

'ls',

'lt',

'ltd',

'lu',

'luxury',

'lv',

'ly',

'ma',

'madrid',

'man',

'management',

'market',

'marketing',

'markets',

'mba',

'mc',

'md',

'me',

'media',

'melbourne',

'memorial',

'men',

'menu',

'mg',

'mil',

'mk',

'ml',

'mm',

'mn',

'mo',

'mobi',

'moe',

'money',

'monster',

'moscow',

'movie',

'mp',

'mq',

'mr',

'ms',

'mt',

'mu',

'museum',

'mv',

'mw',

'mx',

'my',

'mz',

'na',

'nagoya',

'name',

'nc',

'ne',

'net',

'network',

'neustar',

'news',

'nf',

'ng',

'ngo',

'ni',

'ninja',

'nl',

'no',

'np',

'nr',

'nrw',

'ntt',

'nu',

'nyc',

'nz',

'observer',

'okinawa',

'om',

'one',

'ong',

'onion',

'onl',

'online',

'ooo',

'org',

'ovh',

'pa',

'page',

'panasonic',

'paris',

'partners',

'parts',

'party',

'pe',

'pet',

'pf',

'pg',

'ph',

'photo',

'photography',

'photos',

'pics',

'pictet',

'pictures',

'pink',

'pioneer',

'pk',

'pl',

'place',

'plus',

'pm',

'pn',

'post',

'pr',

'press',

'pro',

'productions',

'properties',

'property',

'ps',

'pt',

'pub',

'pw',

'py',

'qa',

'quebec',

'racing',

'radio',

're',

'realestate',

'recipes',

'red',

'reise',

'reisen',

'report',

'rest',

'restaurant',

'review',

'reviews',

'ricoh',

'rio',

'rip',

'ro',

'rocks',

'rs',

'ru',

'rugby',

'ruhr',

'run',

'rw',

'rwe',

'sa',

'saarland',

'sale',

'salon',

'sandvik',

'sb',

'sbi',

'sc',

'schmidt',

'school',

'schule',

'schwarz',

'science',

'scot',

'sd',

'se',

'security',

'sener',

'services',

'sexy',

'sg',

'sh',

'sharp',

'shop',

'shopping',

'show',

'si',

'site',

'sk',

'ski',

'sky',

'sl',

'sm',

'sn',

'sncf',

'so',

'social',

'softbank',

'software',

'solar',

'solutions',

'sony',

'space',

'sport',

'sr',

'ss',

'st',

'stada',

'stockholm',

'store',

'stream',

'studio',

'study',

'style',

'su',

'supply',

'support',

'surf',

'sv',

'swiss',

'sx',

'sy',

'systems',

'sz',

'taipei',

'tatar',

'tattoo',

'tax',

'tc',

'td',

'team',

'tech',

'technology',

'tel',

'tennis',

'test',

'tf',

'tg',

'th',

'theater',

'tips',

'tirol',

'tj',

'tk',

'tl',

'tm',

'tn',

'to',

'today',

'tokyo',

'tools',

'top',

'toshiba',

'total',

'tours',

'town',

'toyota',

'tr',

'training',

'travel',

'trust',

'trv',

'tt',

'tube',

'tv',

'tw',

'tz',

'ua',

'ug',

'uk',

'university',

'uno',

'uol',

'us',

'uy',

'uz',

'V4',

'va',

'vanguard',

'vc',

've',

'vegas',

'ventures',

'vet',

'vg',

'vi',

'video',

'vig',

'villas',

'vip',

'vision',

'vlaanderen',

'vn',

'vodka',

'vote',

'vu',

'wales',

'watch',

'webcam',

'weber',

'website',

'weir',

'wf',

'whoswho',

'wien',

'wiki',

'wine',

'work',

'works',

'world',

'ws',

'wtf',

'xxx',

'xyz',

'yandex',

'ye',

'yoga',

'youtube',

'yt',

'za',

'zm',

'zone',

'zuerich',

'zw',

'бел',

'онлайн',

'орг',

'рус',

'рф',

'срб',

'укр',

'հայ',

'قطر',

'中国',

'bugatti',

'an',

'')

)

SELECT CONCAT( '# ' , SUBSTRING_INDEX(SUBSTRING_INDEX(externallinks.el_to_domain_index,'.',1),'/',-1) , '  {{Bearbeiten|',p.page_title ,'|text=',p.page_title,'}}'  )as output 

  ,  externallinks.el_to_domain_index 

  ,  externallinks.el_to_path

FROM  externallinks INNER JOIN p

  ON p.page_id = externallinks.el_from

INNER JOIN TLDh 

  ON externallinks.el_to_domain_index = TLDh.el_to_domain_index

ORDER BY 1;