Untitled query #7405

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

Category:National Hockey League roster templates

SQL

use frwiki_p;
SELECT pp_value, enp.page_title, (select count(l.ll_lang)
							from langlinks l
							where enp.page_id=l.ll_from
							group by l.ll_from),
                            
                            (select cl2.cl_to
						from categorylinks cl2
						where enp.page_id=cl2.cl_from
                    and cl2.cl_to rlike "Naissance_en_(janvier|février|mars|avril|mai|juin|juillet|août|septembre|octobre|novembre|décembre)_(\\d+)"
                             and cl2.cl_type='page'
						and enp.page_is_redirect=0 and enp.page_namespace=0 limit 1)
                            
                            
FROM page enp, page_props, wikidatawiki_p.page wdp
LEFT JOIN wikidatawiki_p.pagelinks wdpl ON wdpl.pl_from = wdp.page_id AND wdpl.pl_title = 'P1146' AND wdpl.pl_namespace = 120
WHERE enp.page_namespace = 0 
AND pp_page = enp.page_id AND pp_propname = 'wikibase_item'
AND wdp.page_title = pp_value AND wdp.page_namespace = 0
AND wdpl.pl_from IS NULL
and enp.page_id in (select tl.tl_from from templatelinks tl
						where tl.tl_title="Infobox_Athlète" and tl_namespace=10 and tl.tl_from_namespace=0)
group by enp.page_title
limit 1000
/*use frwiki_p;
SELECT pp_value, enp.page_title, (select count(l.ll_lang)
							from langlinks l
							where enp.page_id=l.ll_from
							group by l.ll_from),
                            
                            (select cl2.cl_to
						from categorylinks cl2
						where enp.page_id=cl2.cl_from
                    and  cl2.cl_to like "Naissance_en%" and cl2.cl_type='page'
						and enp.page_is_redirect=0 and enp.page_namespace=0 limit 1)
                            
                            
FROM page enp, page_props, wikidatawiki_p.page wdp
LEFT JOIN wikidatawiki_p.pagelinks wdpl ON wdpl.pl_from = wdp.page_id AND wdpl.pl_title = 'P1409' AND wdpl.pl_namespace = 120
WHERE enp.page_namespace = 0 
AND pp_page = enp.page_id AND pp_propname = 'wikibase_item'
AND wdp.page_title = pp_value AND wdp.page_namespace = 0
AND wdpl.pl_from IS NULL
and enp.page_id in (select tl.tl_from from templatelinks tl
						where tl.tl_title="Infobox_Athlète" and tl_namespace=10 and tl.tl_from_namespace=0)
group by enp.page_title
limit 1000



Infobox Cycliste
use enwiki_p;
SELECT pp_value, enp.page_title, (select count(l.ll_lang)
							from langlinks l
							where enp.page_id=l.ll_from
							group by l.ll_from),
                            
                            (select cl2.cl_to
						from categorylinks cl2
						where enp.page_id=cl2.cl_from
                    and  cl2.cl_to like "%births" and cl2.cl_type='page'
						and enp.page_is_redirect=0 and enp.page_namespace=0)
                            
                            
FROM page enp, page_props, wikidatawiki_p.page wdp
LEFT JOIN wikidatawiki_p.pagelinks wdpl ON wdpl.pl_from = wdp.page_id AND wdpl.pl_title = 'P2481' AND wdpl.pl_namespace = 120
WHERE enp.page_namespace = 0 
AND pp_page = enp.page_id AND pp_propname = 'wikibase_item'
AND wdp.page_title = pp_value AND wdp.page_namespace = 0
AND wdpl.pl_from IS NULL
and enp.page_id in (select tl.tl_from from templatelinks tl
						where tl.tl_title="Infobox_ice_hockey_player" and tl_namespace=10 and tl.tl_from_namespace=0)
group by enp.page_title
limit 1000



use enwiki_p;
SELECT pp_value, enp.page_title, (select count(l.ll_lang)
							from langlinks l
							where enp.page_id=l.ll_from
							group by l.ll_from),
                            
                            (select cl2.cl_to
						from categorylinks cl2
						where enp.page_id=cl2.cl_from
                    and  cl2.cl_to like "%births" and cl2.cl_type='page'
						and enp.page_is_redirect=0 and enp.page_namespace=0)
                            
                            
FROM page enp, page_props, wikidatawiki_p.page wdp
LEFT JOIN wikidatawiki_p.pagelinks wdpl ON wdpl.pl_from = wdp.page_id AND wdpl.pl_title = 'P2481' AND wdpl.pl_namespace = 120
WHERE enp.page_namespace = 0 
AND pp_page = enp.page_id AND pp_propname = 'wikibase_item'
AND wdp.page_title = pp_value AND wdp.page_namespace = 0
AND wdpl.pl_from IS NULL
and enp.page_id in (select cl_from
							from categorylinks
							where cl_to="Hockey_Hall_of_Fame_inductees")
group by enp.page_title
limit 1000

use enwiki_p;
SELECT pp_value, enp.page_title, (select count(l.ll_lang)
							from langlinks l
							where enp.page_id=l.ll_from
							group by l.ll_from),
                            
                            (select cl2.cl_to
						from categorylinks cl2
						where enp.page_id=cl2.cl_from
                    and  cl2.cl_to like "%births" and cl2.cl_type='page'
						and enp.page_is_redirect=0 and enp.page_namespace=0)
                            
                            
FROM page enp, page_props, wikidatawiki_p.page wdp
LEFT JOIN wikidatawiki_p.pagelinks wdpl ON wdpl.pl_from = wdp.page_id AND wdpl.pl_title = 'P2481' AND wdpl.pl_namespace = 120
WHERE enp.page_namespace = 0 
AND pp_page = enp.page_id AND pp_propname = 'wikibase_item'
AND wdp.page_title = pp_value AND wdp.page_namespace = 0
AND wdpl.pl_from IS NULL
and enp.page_id in (select p.page_id from pagelinks pl
						join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
						where pl.pl_from in (8984488,
9490059,
9033247,
9411659,
9411874,
9412067,
9412333,
9412739,
9412966,
9413275,
9428161,
9428379,
9429191,
9429925,
9430187,
9430409,
9488869,
9489226,
9489423,
9489591,
9489746,
9489887,
9467293,
9466904,
9466595,
9459664,
9459397,
9411188,
9459079,
9411431) and pl.pl_namespace=0)
group by enp.page_title
limit 1000
*/

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...