Mainspace pages linking to non-WP pages in [[Category:Wikipedia essays]] or subcats

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

[[WP:RAQ#Mainspace pages that link to a page in a specific category]] circa 27 September 2019.

This can be done in one query with a multi-column subquery, but that's unusably slow.

This only lists mainspace pages linking to non-Wikipedia: pages in the category tree, which is by far the slowest part. Mainspace pages linking to Wikipedia: pages in the tree are at query 39228.

SQL

SELECT page_title
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE page_namespace = 0
AND (pl_namespace, pl_title) IN
   (SELECT page_namespace AS pl_namespace, page_title AS pl_title
    FROM page
    JOIN categorylinks ON page_id = cl_from
    WHERE cl_to IN (
-- From <https://petscan.wmflabs.org/?&categories=Wikipedia%20essays&depth=10&ns%5B14%5D=1&doit=> with 'Wikipedia_essays' added in manually
'Essay_header_templates',
'Essays_about_Wikipedian_fallacies',
'Essays_about_what_Wikipedia_is',
'Essays_by_Ritchie333',
'Humorous_Wikipedia_essays',
'Humorous_user_essays',
'Orphaned_Wikipedia_essays',
'Uncategorized_essays',
'Unsubmitted_Wikipedian_responses_to_RfA_Review',
'User_criteria_for_adminship',
'User_essays',
'User_essays_about_adminship',
'User_essays_on_BLP',
'User_essays_on_IRC',
'User_essays_on_civility',
'User_essays_on_deletion',
'User_essays_on_dispute_resolution',
'User_essays_on_style',
'WikiProject_India_essays',
'WikiProject_U.S._Roads_essays',
'WikiProject_advice',
'WikiProject_content_advice',
'WikiProject_notability_advice',
'WikiProject_style_advice',
'Wikipedia_behavioral_essays',
'Wikipedia_civility_essays',
'Wikipedia_competence_essays',
'Wikipedia_deletion_essays',
'Wikipedia_essay_templates',
'Wikipedia_essays',
'Wikipedia_essays_about_BLP',
'Wikipedia_essays_about_German',
'Wikipedia_essays_about_accessibility',
'Wikipedia_essays_about_administration',
'Wikipedia_essays_about_adminship',
'Wikipedia_essays_about_arbitration',
'Wikipedia_essays_about_article_formatting',
'Wikipedia_essays_about_building_the_encyclopedia',
'Wikipedia_essays_about_categorization',
'Wikipedia_essays_about_consensus',
'Wikipedia_essays_about_editing',
'Wikipedia_essays_about_experts_and_expertise',
'Wikipedia_essays_about_gender',
'Wikipedia_essays_about_infoboxes',
'Wikipedia_essays_about_lists',
'Wikipedia_essays_about_navigation',
'Wikipedia_essays_about_notability',
'Wikipedia_essays_about_original_research',
'Wikipedia_essays_about_redirecting',
'Wikipedia_essays_about_reliable_sources',
'Wikipedia_essays_about_stubs',
'Wikipedia_essays_about_style',
'Wikipedia_essays_about_templates',
'Wikipedia_essays_about_verification',
'Wikipedia_essays_and_information_pages_about_the_Manual_of_Style',
'Wikipedia_essays_explaining_processes',
'Wikipedia_essays_identifying_problems_and/or_solutions',
'Wikipedia_essays_introducing_or_defining_new_terms',
'Wikipedia_essays_outlining_goals',
'Wikipedia_essays_supporting_editor_endurance',
'Wikipedia_how-to_essays',
'Wikipedia_neutrality_essays',
'Wikipedia_style_guideline_proposals',
'Wikipedia_vandalism_essays',
'Wikipedia_warning_essays',
'Wikipedian_responses_to_RfA_Review')
   );

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...