Fork of Hewiki new disambiguations by IKhitron
This query is marked as a draft This query has been published by Virant.

SQL

x
 
select concat(page, "{{כ}} ([[", disambig, "]])") from
(select distinct disambig, page, ns from
((select concat('# [[', case p1.page_namespace when 0 then '' when 6 then ':קובץ:' when 10 then 'תבנית:'
       else 'פורטל:' end,
       replace(p1.page_title, "_", " "), ']]') as page,
       replace(p2.page_title, "_", " ") as disambig,
       p1.page_namespace as ns
from pagelinks join page as p1 join page as p2 join redirect
on p1.page_id = pl_from
and pl_title = p2.page_title
and p2.page_id = rd_from
where pl_namespace = 0
and p1.page_namespace in (0, 6, 10, 100)
and rd_namespace = 0
and rd_title in
(select page_title
from page join categorylinks
on page_namespace = 0
and page_id = cl_from
and cl_to = 'פירושונים')
and not exists
(select * from categorylinks as cl1
 where cl1.cl_from = p1.page_id
 and cl1.cl_to = 'פירושונים')
and not p2.page_title like "%_(פירושונים)"
and p1.page_is_redirect = 0
and not exists
(select * from templatelinks join linktarget
 where tl_from = p1.page_id
 and tl_target_id = lt_id
 and lt_title = "פירושון")
union
select concat('# [[', case p1.page_namespace when 0 then '' when 6 then ':קובץ:' when 10 then 'תבנית:'
       else 'פורטל:' end,
       replace(p1.page_title, "_", " "), ']]') as page,
       replace(p2.page_title, "_", " ") as disambig,
       p1.page_namespace as ns
from page as p1 join pagelinks on p1.page_id = pagelinks.pl_from and p1.page_namespace in (0, 6, 10, 100)
join page as p2 on pagelinks.pl_namespace = p2.page_namespace and pagelinks.pl_title = p2.page_title
where p1.page_is_redirect = 0
and not p2.page_title like "%_(פירושונים)"
and exists
(select * from categorylinks
 where p2.page_id = cl_from
 and cl_to = 'פירושונים')
and not exists
(select * from categorylinks
 where p1.page_id = cl_from
 and cl_to = 'פירושונים')
and not exists
(select * from templatelinks join linktarget
 where tl_from = p1.page_id
 and tl_target_id = lt_id
 and lt_title = "פירושון")
and not (p1.page_namespace = 10
         and p1.page_title in ('לטיפול_דחוף', 'לטיפול_דחוף/שינויים_אחרונים'))) as t)
order by disambig, ns, page) tt
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...