Edgars2007's testing area - links to orphan articles

SQL

use lvwiki_p;
SELECT pl.pl_namespace, pl.pl_title, count(pl.pl_from), pl.pl_from_namespace#p.page_title, COUNT(pl.pl_from)
from pagelinks pl
where pl.pl_namespace=0 

and pl.pl_from_namespace=0
and /*pl.pl_from_namespace=0 and  - svarīgi arī veidnēs izvākt*/ pl.pl_title in (SELECT p2.page_title
from pagelinks pl2
join page p2 on pl2.pl_from=p2.page_id
where p2.page_is_redirect=1 and pl2.pl_namespace=0
		and pl2.pl_from_namespace=0
      and /*pl2.pl_from_namespace=0 and  - svarīgi arī veidnēs izvākt*/pl2.pl_title in (SELECT p3.page_title
					FROM page p3
					INNER JOIN categorylinks c3 ON p3.page_id = c3.cl_from AND c3.cl_to = "Izolētie_raksti"
					INNER JOIN pagelinks pl3 ON p3.page_title = pl3.pl_title AND p3.page_namespace = pl3.pl_namespace
					INNER JOIN page p23 ON p23.page_id = pl3.pl_from
					AND pl3.pl_from_namespace = 0)
                                                                                                         
                                                                                                         )
group by pl.pl_title
order by count(pl.pl_from)
/*
==1. kārta  - dabū saites uz pāradresāciju - kvalifikācijas raksts: pl_from=105015
use lvwiki_p;
SELECT *#p.page_title, COUNT(pl.pl_from)
from pagelinks pl
where pl.pl_namespace=0 and pl.pl_from_namespace=0 and pl.pl_title="Arda_Turan"


SELECT *#p.page_title, COUNT(pl.pl_from)
from pagelinks pl
where pl.pl_namespace=0 and pl.pl_from_namespace=0 and pl.pl_title="Arda_Turan"
--1. kārta

==2. kārta == visas pāradresācijas uz lapu
use lvwiki_p;
SELECT p.page_title
from pagelinks pl
join page p on pl.pl_from=p.page_id
where p.page_is_redirect=1 and pl.pl_namespace=0 and pl.pl_from_namespace=0 and pl.pl_title="Arda_Turans"




from pagelinks pl# ON p.page_title = pl.pl_title AND p.page_namespace = pl.pl_namespace
left join redirect r on r.rd_from=pl.pl_from
#šo pagelinks jāsavieno ar saitēm uz pāradresācijām
join page p on r.rd_from=p.page_id
#join pagelinks p2 on p.page_title=p2.pl_title

where pl.pl_title="Arda_Turans" and pl.pl_namespace=0 and rd_from IS NOT NULL
(select page_title from page
 where page_is_redirect=1 and page_id in 
 										(select rd_from from redirect */
SQL

Checking query status...
