Fork of Mainspace pages linking to non-WP pages in [[Category:Wikipedia essays]] or subcats by Cryptic
This query is marked as a draft This query has been published by Cryptic.

SQL

​x
 
-- Redirects in that category tree - mainly to show that there's little, if any, overlap with queries 39227 and 39228
SELECT DISTINCT CONCAT('[[', CASE page_namespace
                               WHEN 2 THEN 'User:'
                               WHEN 4 THEN 'Wikipedia:'
                               ELSE CONCAT('{{ns:', page_namespace, '}}:')
                             END, page_title, ']]') AS 'Redirects categorized as Wikipedia_essays (or subcats)'
FROM page
JOIN categorylinks ON page_id = cl_from
-- From <https://petscan.wmflabs.org/?&categories=Wikipedia%20essays&depth=10&ns%5B14%5D=1&doit=> with 'Wikipedia_essays' added in manually
WHERE cl_to IN ('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')
AND page_is_redirect = 1;
​
-- Summary of counts
SELECT COUNT(page_title),
       CONCAT('[[', CASE pl_namespace
                      WHEN 0 THEN ''
                      WHEN 4 THEN 'Wikipedia:'
                      ELSE CONCAT('{{ns:', pl_namespace, '}}:')
                    END, pl_title, ']]') AS 'redirect to page categorized as Wikipedia_essays (or subcats)'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0
  AND (pl_namespace, pl_title) IN
    -- subquery: redirects to pages in that category tree
    (SELECT p2.page_namespace AS pl_namespace, p2.page_title AS pl_title
     FROM redirect
     JOIN page p2 ON p2.page_id = rd_from
     WHERE (rd_namespace, rd_title) IN
       -- subquery: pages in that category tree
       (SELECT p1.page_namespace AS rd_namespace, p1.page_title AS rd_title
        FROM page p1
        JOIN categorylinks ON p1.page_id = cl_from
        WHERE cl_to IN ('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')
       )
    )
GROUP BY pl_namespace, pl_title
ORDER BY COUNT(page_title) DESC, pl_namespace ASC, pl_title ASC;
​
SELECT CONCAT('[[', page_title, ']]') AS 'mainspace page that links to...',
       CONCAT('[[', CASE pl_namespace
                      WHEN 0 THEN ''
                      WHEN 4 THEN 'Wikipedia:'
                      ELSE CONCAT('{{ns:', pl_namespace, '}}:')
                    END, pl_title, ']]') AS 'redirect to page categorized as Wikipedia_essays (or subcats)'
FROM pagelinks
JOIN page ON page_id = pl_from
WHERE pl_from_namespace = 0
  AND (pl_namespace, pl_title) IN
    -- subquery: redirects to pages in that category tree
    (SELECT p2.page_namespace AS pl_namespace, p2.page_title AS pl_title
     FROM redirect
     JOIN page p2 ON p2.page_id = rd_from
     WHERE (rd_namespace, rd_title) IN
       -- subquery: pages in that category tree
       (SELECT p1.page_namespace AS rd_namespace, p1.page_title AS rd_title
        FROM page p1
        JOIN categorylinks ON p1.page_id = cl_from
        WHERE cl_to IN ('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 pl_namespace ASC, pl_title ASC, page_title ASC;
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
All SQL code is licensed under CC0 License.

Checking query status...