Portal subpages with existing base page - Quarry

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

Portal subpages (with at least one non-deleted revision) whose base page exists.

For [[Wikipedia talk:WikiProject Portals#Identifying old-style portals]] circa 11 April 2019.

SQL

SELECT
    CONCAT('[[Portal:', basepage.page_title, ']]') AS 'basepage',
    CONCAT('[[Portal:', subpage.page_title, ']]') AS 'subpage'
FROM page subpage
JOIN page basepage
    ON basepage.page_namespace = 100
    AND basepage.page_title = REGEXP_REPLACE(subpage.page_title, '/.*', '')
WHERE subpage.page_namespace = 100
    AND subpage.page_title LIKE '%/%';

​x
 
SELECT    CONCAT('[[Portal:', basepage.page_title, ']]') AS 'basepage',    CONCAT('[[Portal:', subpage.page_title, ']]') AS 'subpage'FROM page subpageJOIN page basepage    ON basepage.page_namespace = 100    AND basepage.page_title = REGEXP_REPLACE(subpage.page_title, '/.*', '')WHERE subpage.page_namespace = 100    AND subpage.page_title LIKE '%/%';​

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.

Checking query status...