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.