Possible Latvia-related categories at Commons, not connected to Wikidata

Fork of Possible Latvia-related categories at Commons, not connected to Wikidata by Edgars2007

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

SQL

use commonswiki_p;
SELECT distinct concat('* [[:Category:',p.page_title,']]')
from pagelinks pl
join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
#left join wbc_entity_usage wb on wb.eu_page_id=p.page_id
where pl.pl_from=43711751 and pl.pl_namespace=14 and p.page_id not in (select wb.eu_page_id
                                                                       from wbc_entity_usage wb
                                                                       where wb.eu_page_id=p.page_id)
     and p.page_title not like "Buildings_in_%" and p.page_title not like "Churches_in%" and p.page_title not like "Churches_of%"
                                                                       	
#and p.page_title in
#(select p2.page_title from enwiki_p.page p2 where p2.page_namespace=0 and p.page_title=p2.page_title and p2.page_is_redirect=0)
#limit 500;
/*
use commonswiki_p;
SELECT distinct concat('* [[:Category:',p.page_title,']]')
from pagelinks pl
join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
join wbc_entity_usage wb on wb.eu_page_id=p.page_id
where pl.pl_from=43711751 and pl.pl_namespace=14
limit 5;
use commonswiki_p;
SELECT distinct concat('* [[:Category:',p.page_title,']]')
from pagelinks pl
join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
left join wbc_entity_usage wb on wb.eu_page_id=p.page_id
where pl.pl_from=43711751 and pl.pl_namespace=14
limit 500;



use commonswiki_p;
SELECT distinct concat('* [[:Category:',p.page_title,']]')
from pagelinks pl
join page p on p.page_title=pl.pl_title and pl.pl_namespace=p.page_namespace
left join wbc_entity_usage wb on wb.eu_page_id=p.page_id
where pl.pl_from=43711751 and pl.pl_namespace=14 and p.page_title in
(select p2.page_title from enwiki_p.page p2 where p2.page_namespace=0 and p.page_title=p2.page_title and p2.page_is_redirect=0)
limit 500;






#concat('* [[:Kategorija:',p.page_title,']]')
#p.page_title
from page p
where p.page_namespace=0
and p.page_title not like "%/%"
and p.page_is_redirect=0
and p.page_id not in (SELECT  eu_page_id
					from wbc_entity_usage wb
					GROUP BY eu_page_id)
                    and p.page_id not in (select ll_from from langlinks ll )
and p.page_id not in (select cl_from
																from categorylinks
																where cl_to="Nozīmju_atdalīšana")
order by p.page_title asc
/*
where p.page_namespace=10
and p.page_title like "Val_%"
and p.page_is_redirect=0
and p.page_id not in (SELECT  eu_page_id
					from wbc_entity_usage wb
					GROUP BY eu_page_id)
                    and p.page_id not in (select ll_from from langlinks ll )
                    
                    
                    
and p.page_title not like "%/%"
and p.page_is_redirect=0
and p.page_id not in (SELECT  eu_page_id
					from wbc_entity_usage wb
					GROUP BY eu_page_id)
                    and p.page_id not in (select ll_from from langlinks ll )*/
#and p.page_namespace=0 and p.page_is_redirect=0 #and p.page_id not in (select cl_from
												#				from categorylinks
												#				where cl_to="Nozīmju_atdalīšana")
#and p.page_title not like "%/%" and p.page_namespace=10 and p.page_id not in (select ll_from from langlinks ll )
#limit 900;
/*
use lvwiki_p;
SELECT
  *
FROM pagelinks pl
join page p ON pl.pl_title = p.page_title
join wbc_entity_usage wb on p.page_id = wb.eu_page_id
#JOIN pagelinks pl ON pl.pl_from = p.page_id
WHERE  pl.pl_namespace = 0 AND p.page_namespace=0 and
pl.pl_from = 290249
GROUP BY eu_page_id
limit 10;
*/

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