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.