Commons categories with no Wikidata sitelink but with en-interwiki with sitelink

Fork of Commons categories with no Wikidata sitelink but with en-interwiki with sitelink by Jheald

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

Commons categories with no Wikidata sitelink but with en-interwiki with sitelink.  Thanks to Matěj Suchánek at wikidata Project Chat, 
https://www.wikidata.org/w/index.php?title=Wikidata%3AProject_chat&type=revision&diff=708005197&oldid=707998591

SQL

USE commonswiki_p;
SELECT p.page_title AS cat_name, ll.ll_title AS en_interwiki, pp1.pp_value AS item, p2.page_namespace AS ns, p2.page_title AS page
FROM page AS p
JOIN langlinks AS ll ON ll.ll_from = p.page_id AND ll.ll_lang = 'en' 
LEFT JOIN page_props AS pp ON pp.pp_page = p.page_id AND pp.pp_propname = 'wikibase_item'
LEFT JOIN enwiki_p.page AS p1 ON p1.page_title = ll.ll_title AND p1.page_namespace = 0
LEFT JOIN enwiki_p.page_props AS pp1 ON pp1.pp_page = p1.page_id AND pp1.pp_propname = 'wikibase_item'
LEFT JOIN page_props AS pp2 ON pp2.pp_value = pp1.pp_value AND pp2.pp_propname = 'wikibase_item'
LEFT JOIN page AS p2 ON p2.page_id = pp2.pp_page
LEFT JOIN templatelinks AS t1 ON t1.tl_from = p.page_id AND t1.tl_from_namespace = 14 AND t1.tl_namespace = 10 AND t1.tl_title = 'Synonym_taxon_category_redirect' 
LEFT JOIN templatelinks AS t2 ON t2.tl_from = p.page_id AND t2.tl_from_namespace = 14 AND t2.tl_namespace = 10 AND t2.tl_title = 'Category_redirect' 
LEFT JOIN templatelinks AS t3 ON t3.tl_from = p.page_id AND t3.tl_from_namespace = 14 AND t3.tl_namespace = 10 AND t3.tl_title = 'artwork' 
LEFT JOIN templatelinks AS t4 ON t4.tl_from = p.page_id AND t4.tl_from_namespace = 14 AND t4.tl_namespace = 10 AND t4.tl_title = 'wikidata_person' 
LEFT JOIN templatelinks AS t5 ON t5.tl_from = p.page_id AND t5.tl_from_namespace = 14 AND t5.tl_namespace = 10 AND t5.tl_title = 'institution' 
LEFT JOIN templatelinks AS t6 ON t6.tl_from = p.page_id AND t6.tl_from_namespace = 14 AND t6.tl_namespace = 10 AND t6.tl_title = 'category_definition' 
LEFT JOIN templatelinks AS t7 ON t7.tl_from = p.page_id AND t7.tl_from_namespace = 14 AND t7.tl_namespace = 10 AND t7.tl_title = 'disambig' 
LEFT JOIN templatelinks AS t8 ON t8.tl_from = p.page_id AND t8.tl_from_namespace = 14 AND t8.tl_namespace = 10 AND t8.tl_title = 'disambiguation' 
LEFT JOIN templatelinks AS t9 ON t9.tl_from = p.page_id AND t9.tl_from_namespace = 14 AND t9.tl_namespace = 10 AND t9.tl_title = 'razločitev' 
LEFT JOIN templatelinks AS t10 ON t10.tl_from = p.page_id AND t10.tl_from_namespace = 14 AND t10.tl_namespace = 10 AND t10.tl_title = 'begriffsklärung'
LEFT JOIN templatelinks AS t11 ON t11.tl_from = p.page_id AND t11.tl_from_namespace = 14 AND t11.tl_namespace = 10 AND t11.tl_title = 'dab'
LEFT JOIN templatelinks AS t12 ON t12.tl_from = p.page_id AND t12.tl_from_namespace = 14 AND t12.tl_namespace = 10 AND t12.tl_title = 'aimai'
LEFT JOIN templatelinks AS t13 ON t13.tl_from = p.page_id AND t13.tl_from_namespace = 14 AND t13.tl_namespace = 10 AND t13.tl_title = 'taxonavigation'
LEFT JOIN templatelinks AS t14 ON t14.tl_from = p.page_id AND t14.tl_from_namespace = 14 AND t14.tl_namespace = 10 AND t14.tl_title = 'Category definition'
LEFT JOIN templatelinks AS t15 ON t15.tl_from = p.page_id AND t15.tl_from_namespace = 14 AND t15.tl_namespace = 10 AND t15.tl_title = 'lepidoptera'
LEFT JOIN templatelinks AS t16 ON t16.tl_from = p.page_id AND t16.tl_from_namespace = 14 AND t16.tl_namespace = 10 AND t16.tl_title = 'coleoptera'
LEFT JOIN templatelinks AS t17 ON t17.tl_from = p.page_id AND t17.tl_from_namespace = 14 AND t17.tl_namespace = 10 AND t17.tl_title = 'taxonavigation'
LEFT JOIN templatelinks AS t18 ON t18.tl_from = p.page_id AND t18.tl_from_namespace = 14 AND t18.tl_namespace = 10 AND t18.tl_title = 'species'
LEFT JOIN templatelinks AS t19 ON t19.tl_from = p.page_id AND t19.tl_from_namespace = 14 AND t19.tl_namespace = 10 AND t19.tl_title = 'species2'
LEFT JOIN templatelinks AS t20 ON t20.tl_from = p.page_id AND t20.tl_from_namespace = 14 AND t20.tl_namespace = 10 AND t20.tl_title = 'wikispecies'


WHERE p.page_namespace=14 AND pp.pp_value IS NULL AND pp1.pp_value IS NOT NULL
AND t1.tl_title IS NULL
AND t2.tl_title IS NULL
AND t3.tl_title IS NULL
AND t4.tl_title IS NULL
AND t5.tl_title IS NULL
AND t6.tl_title IS NULL
AND t7.tl_title IS NULL
AND t8.tl_title IS NULL
AND t9.tl_title IS NULL
AND t10.tl_title IS NULL
AND t11.tl_title IS NULL
AND t12.tl_title IS NULL
AND t13.tl_title IS NULL
AND t14.tl_title IS NULL
AND t15.tl_title IS NULL
AND t16.tl_title IS NULL
AND t17.tl_title IS NULL
AND t18.tl_title IS NULL
AND t19.tl_title IS NULL
AND t20.tl_title IS NULL
ORDER BY cat_name
#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...