Untitled query #1276

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

# test
# See also https://upload.wikimedia.org/wikipedia/commons/f/f7/MediaWiki_1.24.1_database_schema.svg
SELECT wp.pp_value, GROUP_CONCAT(wp.wiki) As wikis FROM 
(
(SELECT pp_value, "ru" As Wiki
FROM ruwiki_p.categorylinks 
JOIN ruwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Умершие_в_2015_году" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "en" As Wiki
FROM enwiki_p.categorylinks 
JOIN enwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="2015_deaths" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION  
(SELECT pp_value, "zh" As Wiki 
FROM zhwiki_p.categorylinks 
JOIN zhwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="2015年逝世" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND cl_sortkey_prefix NOT LIKE "2%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION  
(SELECT pp_value, "fr" As Wiki 
FROM frwiki_p.categorylinks 
JOIN frwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Mort_récente" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND cl_sortkey_prefix NOT LIKE "2%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "de" As Wiki 
FROM dewiki_p.categorylinks 
JOIN dewiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Gestorben_2015" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND cl_sortkey_prefix NOT LIKE "2%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "fi" As Wiki 
FROM fiwiki_p.categorylinks 
JOIN fiwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Vuonna_2015_kuolleet" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "hu" As Wiki 
FROM huwiki_p.categorylinks 
JOIN huwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="2015-ben_elhunyt_személyek" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "it" As Wiki 
FROM itwiki_p.categorylinks 
JOIN itwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Morti_nel_2015" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "pl" As Wiki 
FROM plwiki_p.categorylinks 
JOIN plwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Zmarli_w_2015" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "pt" As Wiki 
FROM ptwiki_p.categorylinks 
JOIN ptwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Mortos_em_2015" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "cs" As Wiki 
FROM cswiki_p.categorylinks 
JOIN cswiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Úmrtí_2015" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "tr" As Wiki 
FROM trwiki_p.categorylinks 
JOIN trwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="2015_yılında_ölenler" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )
UNION
(SELECT pp_value, "da" As Wiki 
FROM dawiki_p.categorylinks 
JOIN dawiki_p.page_props ON pp_page=cl_from 
WHERE cl_to="Døde_i_2015" 
and cl_sortkey_prefix NOT LIKE " %" 
AND cl_sortkey_prefix NOT LIKE "*%" 
AND pp_propname="wikibase_item" AND cl_type="page" )

  
) As wp  
WHERE wp.pp_value NOT IN (SELECT page_title FROM wikidatawiki_p.page JOIN wikidatawiki_p.pagelinks 
        			ON page_id=pl_from WHERE pl_title = "P570" AND pl_namespace = 120) 
GROUP BY wp.pp_value
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...
