wikidatawiki Alias equal to label

Query breakdown:
- inner 2 queries (t1 & t2): find recently changed items with labels and aliases and their languages
- middle query (t3): find items that use the same text in the same language as both its label and as an alias
- outer query: lookup the texts themselves and the language codes and group them by item ID

Takes ½-2 hours w/o recentchanges, and ½-1 hours with.

SQL

SELECT wbx_text AS text, CONCAT('Q', alias.wbit_item_id) AS `item ID`
FROM (
	SELECT wbxl_id, wbxl_text_id, wbit_item_id
	FROM wbt_term_in_lang, wbt_text_in_lang, wbt_item_terms
	WHERE
	    wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='alias') AND
	    wbtl_text_in_lang_id=wbxl_id AND
	    wbxl_language='nl' AND
  		wbtl_id=wbit_term_in_lang_id
    ) AS alias, (
	SELECT wbxl_id, wbit_item_id
	FROM wbt_term_in_lang, wbt_text_in_lang, wbt_item_terms
	WHERE
	    wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='label') AND
	    wbtl_text_in_lang_id=wbxl_id AND
	    wbxl_language='nl' AND
  		wbtl_id=wbit_term_in_lang_id
    ) AS label,
    wbt_text
WHERE
    alias.wbxl_id=label.wbxl_id AND
    alias.wbit_item_id=label.wbit_item_id AND
    alias.wbxl_text_id=wbx_id
LIMIT 10000

SQL

Checking query status...