Orphaned archive pages

create a list of talk pages that are redirects with non-redirect archive subpages, mainly to find pages that have been moved improperly without their subpages
For simplicity sakes, the archive subpages must follow the ' /Archive %(counter)d ' format to be picked up

SQL

select CONCAT("{{User:Aidan9382/template/ArchiveDumpEntry|1=",SUBSTR(page_title,1,LENGTH(page_title)-10),"}}") as basepage_title
from page
where page_namespace = 1
and page_is_redirect = 0
-- and page_random < 0.05 -- Reduce calculation time when testing
and page_title rlike "/Archive_[1-9]$" -- Covers enough cases
and exists (
  select 1
  from page as basepage
  where basepage.page_namespace = 1
  and basepage.page_is_redirect = 1
  and basepage.page_title = SUBSTR(page.page_title,1,LENGTH(page.page_title)-10)
)
order by basepage_title asc;

SQL

Checking query status...