SQL
AخA
USE stopcommonswiki_p;
SELECT CONCAT ('[[Category:', REPLACE (page_title,'_',' '), ']]') AS touchlist FROM page
LEFT JOIN categorylinks ON cl_from = page_id
WHERE page_namespace = 14
AND page_is_redirect = 0
AND page_title NOT IN ('Disambiguation_of_churches_in_Sweden',
'Disambiguation_of_temples_in_Japan',
'Non-empty_disambiguation_categories',
'Tagged_ambiguous')
AND ((cl_to = 'Disambiguation' #cats from here
AND EXISTS (SELECT 1 FROM categorylinks #which are not empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not already listed...
WHERE cl_from = page_id
AND cl_to = 'Non-empty_disambiguation_categories') #...here
)
OR (cl_to = 'Non-empty_disambiguation_categories' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
)
OR (cl_to = 'Non-empty_category_redirects' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
)
OR (cl_to = 'Images_from_the_Geograph_British_Isles_project_needing_categories_by_grid_square' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not already listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
AND EXISTS (SELECT 1 FROM templatelinks #and are...
WHERE tl_from = page_id
AND tl_namespace = 2
AND tl_title = 'Avicennasis/emptycat') #...transcluded tagged speedy
)
OR (cl_to = 'Files_moved_from_de.wikipedia_to_Commons_requiring_review' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not already listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
AND EXISTS (SELECT 1 FROM templatelinks #and are...
WHERE tl_from = page_id
AND tl_namespace = 10
AND tl_title = 'BotMoveToCommonsHeader') #...transcluded tagged speedy
)
OR (cl_to LIKE 'Media_needing_categories_%' #or cats from here
AND NOT EXISTS (SELECT 1 FROM categorylinks #which are already empty
WHERE cl_to = page_title)
AND NOT EXISTS (SELECT 1 FROM categorylinks #and are not already listed...
WHERE cl_from = page_id
AND cl_to = 'Other_speedy_deletions') #...here
AND EXISTS (SELECT 1 FROM templatelinks #and are...
WHERE tl_from = page_id
AND tl_namespace = 10
AND tl_title = 'UncategorizedHeader') #...transcluded tagged speedy
)
OR cl_to = 'Broken_category_redirects' #or from here precautionary
OR cl_to = 'Cfd_requests_with_missing_discussion_page' #or from here precautionary
)
GROUP BY page_title;
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.