orphan articles v2 [lvwiki]

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

USE lvwiki_p;
select *
from pagelinks
where pl_from=page_id  and pl_namespace=0 and pl_from_namespace=0
limit 100

USE lvwiki_p;
select *
from page p
where page_is_redirect=0 and page_namespace=0
	and not exists (select * from categorylinks c where p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti")
    and p.page_id not in  (select distinct pl.pl_from from pagelinks pl where pl.pl_namespace=0 and pl.pl_from_namespace=0)
	and not exists (select * from categorylinks c2 where p.page_id = c2.cl_from AND c2.cl_to = "Nozīmju_atdalīšana")
/*
Valērijs Burē
InnoDB
*/

SQL

USE lvwiki_p;
select *
from page p
where page_is_redirect=0 and page_namespace=0
	and page_title in ("Anna_no_\"Zaļajiem_jumtiem\"","100._gads_p.m.ē.","Laipa_(mākslas_galerija)","Izlaiduma_cepure")
	and not exists (select * from categorylinks c where p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti")
    and not exists (select * from pagelinks pl where pl_title=page_title and pl.pl_namespace=0 and pl.pl_from_namespace=0
                   and pl_from not in (select pi.page_id from page pi where pi.page_is_redirect=1)
                    #šeit vēl pielikt, ka tas nav disambigs
                    
                    
                    and pl.pl_from not in  (select p23.page_id
from page p23
where p23.page_namespace=0
    and not exists (select * from pagelinks pl12 where pl12.pl_title=p23.page_title and pl12.pl_namespace=0 and pl12.pl_from_namespace=0
                   and pl12.pl_from not in (select pi2.page_id from page pi2 where pi2.page_is_redirect=1)
                   and pl12.pl_from not in (select c22.cl_from from categorylinks c22 where c22.cl_to = "Nozīmju_atdalīšana")
                   ))
                    
                    
                    
                    
                   
                   )
	and not exists (select * from categorylinks c2 where p.page_id = c2.cl_from AND c2.cl_to = "Nozīmju_atdalīšana")
/*

100. gads p.m.ē. - uz rakstu ved redirecti, uz kuriem ir saites
Laipa_(mākslas_galerija)

Izlaiduma_cepure - šim jāpaliek, uz redirect nav linku

------------------
testēt ar 'kantija', '100 pme'- uz kantiju nav saišu
select *
from page p
where page_namespace=0
    and not exists (select * from pagelinks pl where pl_title=page_title and pl.pl_namespace=0 and pl.pl_from_namespace=0
                   and pl_from not in (select pi.page_id from page pi where pi.page_is_redirect=1)
                   and pl_from not in (select c2.cl_from from categorylinks c2 where c2.cl_to = "Nozīmju_atdalīšana")
                   )
---------------------


USE lvwiki_p;
select *
from page p
where page_is_redirect=0 and page_namespace=0
	and page_title in ("Anna_no_\"Zaļajiem_jumtiem\"","100._gads_p.m.ē.","Laipa_(mākslas_galerija)","Izlaiduma_cepure")
	and not exists (select * from categorylinks c where p.page_id = c.cl_from AND c.cl_to = "Izolētie_raksti")
    and not exists (select * from pagelinks pl where pl_title=page_title and pl.pl_namespace=0 and pl.pl_from_namespace=0
                   and pl_from not in (select pi.page_id from page pi where pi.page_is_redirect=1)
                    #šeit vēl pielikt, ka tas nav disambigs
                   
                   )
	and not exists (select * from categorylinks c2 where p.page_id = c2.cl_from AND c2.cl_to = "Nozīmju_atdalīšana")
*/

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.

SQL

Checking query status...