wikidatawiki Alias equal to label

This query is marked as a draft This query has been published by Bdijkstra.

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 *
FROM
	(SELECT wbit_item_id, wbtl_type_id, wbtl_text_in_lang_id
    FROM wbt_item_terms, wbt_term_in_lang
    WHERE wbit_term_in_lang_id=wbtl_id AND wbtl_type_id IN (SELECT wby_id FROM wbt_type WHERE wby_name IN ('label','alias'))) t1,
	(SELECT wbit_item_id, wbtl_type_id, wbtl_text_in_lang_id
    FROM wbt_item_terms, wbt_term_in_lang
    WHERE wbit_term_in_lang_id=wbtl_id AND wbtl_type_id IN (SELECT wby_id FROM wbt_type WHERE wby_name IN ('label','alias'))) t2,
WHERE
	t1.wbit_item_id=t2.wbit_item_id AND
    t1.wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='label') AND
    t2.wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='alias') AND
    t1.wbtl_text_in_lang_id=t2.wbtl_text_in_lang_id
LIMIT 10

/*
SELECT
	GROUP_CONCAT(DISTINCT wbx_text) AS text,
    GROUP_CONCAT(DISTINCT wbxl_language) AS lang,
    CONCAT('Q', lit.wbit_item_id) AS `item ID`
FROM
	wbt_item_terms lit,
    wbt_item_terms ait,
    (SELECT wbtl_id, wbtl_text_in_lang_id FROM wbt_term_in_lang WHERE wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='alias')) ltl,
    (SELECT wbtl_id, wbtl_text_in_lang_id FROM wbt_term_in_lang WHERE wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='label')) atl,
    wbt_text_in_lang, wbt_text
WHERE
	lit.wbit_item_id=ait.wbit_item_id AND
    lit.wbit_term_in_lang_id=ltl.wbtl_id AND
    ait.wbit_term_in_lang_id=atl.wbtl_id AND
    ltl.wbtl_text_in_lang_id=atl.wbtl_text_in_lang_id AND
    ltl.wbtl_text_in_lang_id=wbxl_id AND
    wbxl_text_id=wbx_id
GROUP BY lit.wbit_item_id
LIMIT 10000*/
/*
SELECT
	GROUP_CONCAT(DISTINCT wbx_text) AS text,
    GROUP_CONCAT(DISTINCT wbxl_language) AS lang,
    CONCAT('Q', lit.wbit_item_id) AS `item ID`
FROM
	wbt_item_terms lit,
    wbt_item_terms ait,
    (SELECT wbtl_id, wbtl_text_in_lang_id FROM wbt_term_in_lang WHERE wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='alias')) ltl,
    (SELECT wbtl_id, wbtl_text_in_lang_id FROM wbt_term_in_lang WHERE wbtl_type_id=(SELECT wby_id FROM wbt_type WHERE wby_name='label')) atl,
    wbt_text_in_lang, wbt_text
WHERE
	lit.wbit_item_id=ait.wbit_item_id AND
    lit.wbit_term_in_lang_id=ltl.wbtl_id AND
    ait.wbit_term_in_lang_id=atl.wbtl_id AND
    ltl.wbtl_text_in_lang_id=atl.wbtl_text_in_lang_id AND
    ltl.wbtl_text_in_lang_id=wbxl_id AND
    wbxl_text_id=wbx_id
GROUP BY lit.wbit_item_id
LIMIT 10000*/
/*
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*/

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...