Fork of ~testing de sister projects broken by Achim55
This query is marked as a draft This query has been published by Achim55.

SQL

AخA
 
USE enwiki_p;
SELECT now() AS timestamp;
#missing page name
(SELECT CONCAT ('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage, CONCAT('→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') AS commonscat
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace = 0
WHERE iwl_prefix ='commons'
AND iwl_title = 'Category:'
)
UNION
#interwikilinks pointing to disambiguations
(SELECT CONCAT ('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage, CONCAT('→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') AS commonscat
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace = 0
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(iwl_title, 10)
AND cp.page_namespace = 14
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation'
WHERE iwl_prefix ='commons'
AND iwl_title LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
    WHERE wcl.cl_from = wp.page_id
    AND wcl.cl_to IN ('Disambiguation_pages',
                      'Buildings_and_structures_disambiguation_pages',
                      'Educational_institution_disambiguation_pages')) #from disambig to disambig is ok
)
UNION
#external links pointing to disambiguations
(SELECT CONCAT('# [[', REPLACE (wp.page_title, '_', ' '), ']]') AS wpage, CONCAT('→ [[:c:Category:', REPLACE (cp.page_title, '_', ' '), ']]') AS commonscat
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace = 0
INNER JOIN commonswiki_p.page cp
ON cp.page_title = SUBSTRING(REPLACE (el_to, '?uselang=en', ''), 39)
AND cp.page_namespace = 14
INNER JOIN commonswiki_p.categorylinks ccl
ON ccl.cl_from = cp.page_id
AND ccl.cl_to = 'Disambiguation'
WHERE el_to LIKE '//commons.wikimedia.org/wiki/Category:%'
AND NOT EXISTS (SELECT 1 FROM categorylinks wcl
    WHERE wcl.cl_from = wp.page_id
    AND wcl.cl_to IN ('Disambiguation_pages',
                      'Buildings_and_structures_disambiguation_pages',
                      'Educational_institution_disambiguation_pages')) #from disambig to disambig is ok
)
ORDER BY wpage
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.
All SQL code is licensed under CC0 License.

Checking query status...