Untitled query #1276

SQL

# Given names with site links different from label
#
# Given names: list of items linked from page [[User:Jura1/first names/items (P31: ok, label: tbd)]]
#
# See also https://upload.wikimedia.org/wikipedia/commons/f/f7/MediaWiki_1.24.1_database_schema.svg
#
use wikidatawiki_p; 
#
SELECT 
	page_title AS item,
	term_language As lang, 
	GROUP_CONCAT(REPLACE(ips_site_id, 'wiki', '')) As WPlangs, 
	term_text As WikidataLabel, 
	TRIM(SUBSTRING_INDEX(CONCAT(ips_site_page, ' ('), '(', 1)) As WP_NormalizedTitle,
	GROUP_CONCAT(CONCAT('[[', REPLACE(ips_site_id, 'wiki', ''), ':', ips_site_page,']]') As WP_ArticleTitles
#, CONCAT('<tr><td> [[', page_title, '|', term_text, ']] <td>', '?' ) As table_output_in_one_cell
#
FROM pagelinks As pl1, page, wb_entity_per_page, wb_terms, wb_items_per_site
#
WHERE pl1.pl_from = 21293912  # id of page [[d:User:Jura1/first names/items (P31: ok, label: tbd)]]
AND pl1.pl_namespace = 0
AND pl1.pl_title = page_title
AND page_id = epp_page_id
AND epp_entity_id = term_entity_id
AND term_language in ('en')
AND term_type = 'label'        # 'label', 'description', 'alias'
AND term_entity_type = 'item'  # 'item' not 'property'
AND term_entity_id = ips_item_id
AND term_text <> ips_site_page
AND term_text <> TRIM(SUBSTRING_INDEX(CONCAT(ips_site_page, ' ('), '(', 1))
AND ips_site_page <> CONCAT('Namnet ', term_text)
AND ips_site_id IN ('enwiki','frwiki','eswiki','nlwiki','dewiki','itwiki','afwiki','anwiki','astwiki','barwiki',
                    'brwiki','cawiki','cowiki','cswiki','cywiki','dawiki','pmswiki','ptwiki','vlswiki''eowiki',
                    'etwiki','euwiki','fiwiki','frpwiki','furwiki','gawiki','gdwiki','glwiki','gswwiki','hrwiki',
                    'huwiki','iawiki','idwiki','iewiki','iswiki','iowiki','kgwiki','lbwiki','liwiki','lijwiki',
                    'mgwiki','minwiki','mswiki','vowiki','wawiki','wowiki','napwiki','nowiki','ndswiki','nnwiki',
                    'nrmwiki','ocwiki','pcdwiki','plwiki','rmwiki','rowiki','scwiki','scnwiki','scowiki','skwiki',
                    'slwiki','svwiki','swwiki','vecwiki','viwiki')
GROUP BY pl1.pl_title, TRIM(SUBSTRING_INDEX(CONCAT(ips_site_page, ' ('), '(', 1))
SQL

Checking query status...
