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

SQL

x
 
with article_redirect as (
  select page_title, rd_title from page
  join redirect on page_id = rd_from
  where page_namespace = 0 and page_is_redirect = 1 and rd_namespace = 0
)
/*
select p1.page_title, p2.page_title from page p1
join page p2 on p2.page_namespace = 0 and p2.page_is_redirect = 0
and p2.page_title = substring(p1.page_title, 1, INSTR(p1.page_title, "_(disambiguation)")-1)
where p1.page_title like '%\_(disambiguation)' and p1.page_namespace = 0 and p1.page_is_redirect = 0
and not exists (
  select 1 from pagelinks where pl_from = p1.page_id
  and pl_namespace = 0 and pl_from_namespace = 0 and pl_title = p2.page_title
)
and not exists (
  select 1 from pagelinks where pl_from = p1.page_id and pl_namespace = 0 and pl_from_namespace = 0
  and pl_title in (
    select page_title from redirect, page where page_id = rd_from and rd_namespace = 0 and rd_title = p2.page_title
  )
)
*/
select p1.page_title, rd_title from page p1
/*
join page p2 on p2.page_namespace = 0 and p2.page_is_redirect = 1
and p2.page_title = substring(p1.page_title, 1, INSTR(p1.page_title, "_(disambiguation)")-1)
join redirect on rd_namespace = 0 and rd_from = p2.page_id
*/
join article_redirect ar1 on ar1.page_title = substring(p1.page_title, 1, INSTR(p1.page_title, "_(disambiguation)")-1)
where p1.page_title like '%\_(disambiguation)' and p1.page_namespace = 0 and p1.page_is_redirect = 0
and not exists (
  select 1 from pagelinks where pl_from = p1.page_id
  and pl_namespace = 0 and pl_from_namespace = 0 and pl_title = ar1.rd_title
)
and not exists (
  select 1 from pagelinks where pl_from = p1.page_id and pl_namespace = 0 and pl_from_namespace = 0
  and pl_title in (
    select ar2.page_title from article_redirect ar2 where ar2.rd_title = ar1.rd_title
  )
)
limit 1
/*
     or p2.page_title in (
       select rd_title from redirect, page where rd_namespace = 0 and rd_from = page_id
       and page_title = substring(p1.page_title, 1, INSTR(p1.page_title, "_(disambiguation)")-1)
     ))
*/
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...