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, alias.wbtl_id AS `alias-id`, label.wbtl_id AS `label-id`
FROM (
	SELECT wbxl_id, wbxl_text_id, wbtl_id
	FROM wbt_term_in_lang, wbt_text_in_lang
	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'
    ) AS alias, (
	SELECT wbxl_id, wbtl_id
	FROM wbt_term_in_lang, wbt_text_in_lang
	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'
    ) AS label,
    wbt_text
WHERE
    alias.wbxl_id=label.wbxl_id AND
    alias.wbxl_text_id=wbx_id
LIMIT 50

SQL

Checking query status...