Fork of Commons categories with no Wikidata sitelink but with de-interwiki with sitelink by Jheald
This query is marked as a draft This query has been published by Jheald.

SQL

x
 
USE commonswiki_p;
SELECT p.page_title AS cat_name, ll.ll_title AS de_interwiki, pp1.pp_value AS item, wbt.term_text AS en_label, p2.page_namespace AS ns, p2.page_title AS page
#SELECT p.page_title AS cat_name, ll.ll_title AS de_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 = 'de' 
LEFT JOIN page_props AS pp ON pp.pp_page = p.page_id AND pp.pp_propname = 'wikibase_item'
LEFT JOIN dewiki_p.page AS p1 ON p1.page_title = ll.ll_title AND p1.page_namespace = 0
LEFT JOIN dewiki_p.page_props AS pp1 ON pp1.pp_page = p1.page_id AND pp1.pp_propname = 'wikibase_item'
JOIN wikidatawiki_p.wb_terms AS wbt ON wbt.term_full_entity_id = pp1.pp_value AND wbt.term_type = 'label' AND wbt.term_language = 'en' 
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' 
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
#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.
All SQL code is licensed under CC0 License.

Checking query status...