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 DISTINCT page_namespace AS 'namespaces with pages in category tree'
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')
ORDER BY page_namespace ASC;

-- Namespace 2: User
SELECT page_title, CONCAT('User:', pl_title) AS 'Links to'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0 -- pages linking from mainspace...
  AND pl_namespace = 2 -- to pages in userspace categorized in...
  AND pl_title IN
    (SELECT page_title
     FROM page
     JOIN categorylinks ON page_id = cl_from
     WHERE page_namespace = 2
       AND 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'));

-- Namespace 3: User talk
SELECT page_title, CONCAT('User_talk:', pl_title) AS 'Links to'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0
  AND pl_namespace = 3
  AND pl_title IN
    (SELECT page_title
     FROM page
     JOIN categorylinks ON page_id = cl_from
     WHERE page_namespace = 3
       AND 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'));

-- Namespace 5: Wikipedia talk
SELECT page_title, CONCAT('Wikipedia_talk:', pl_title) AS 'Links to'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0
  AND pl_namespace = 5
  AND pl_title IN
    (SELECT page_title
     FROM page
     JOIN categorylinks ON page_id = cl_from
     WHERE page_namespace = 5
       AND 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'));

-- Namespace 10: Template
SELECT page_title, CONCAT('Template:', pl_title) AS 'Links to'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0
  AND pl_namespace = 10
  AND pl_title IN
    (SELECT page_title
     FROM page
     JOIN categorylinks ON page_id = cl_from
     WHERE page_namespace = 10
       AND 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'));

-- Namespace 12: Help
SELECT page_title, CONCAT('Help:', pl_title) AS 'Links to'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0
  AND pl_namespace = 12
  AND pl_title IN
    (SELECT page_title
     FROM page
     JOIN categorylinks ON page_id = cl_from
     WHERE page_namespace = 12
       AND 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'));

-- Namespace 14: Category
SELECT page_title, CONCAT('Category:', pl_title) AS 'Links to'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0
  AND pl_namespace = 14
  AND pl_title IN
    (SELECT page_title
     FROM page
     JOIN categorylinks ON page_id = cl_from
     WHERE page_namespace = 14
       AND 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...