missing articles (redlinks) starting with given string on etwiki

Fork of Missing articles (redlinks) starting with given string on cswiki by Utar

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

SQL

USE etwiki_p;

#ECT p.page_id "id", p.page_namespace "from NS", p.page_title "from", pl.pl_namespace "to NS", pl.pl_title "to"
#SELECT pl.pl_title "redlink", COUNT(*) "count"
SELECT pl.pl_title "redlink", count(*)
FROM page p
INNER JOIN pagelinks pl
	ON pl.pl_from = p.page_id
WHERE pl.pl_title NOT IN # to missing articles
		(SELECT pp.page_title
         FROM page pp)
    AND p.page_namespace IN (0,4,14) # from main, Wikipedia, Category 
	AND pl.pl_namespace = 0 # to main 
 	AND pl.pl_title LIKE "%ministeerium%" # to linked articles starting with this string
#GROUP BY p.page_id, p.page_namespace, p.page_title, pl.pl_namespace, pl.pl_title
GROUP BY pl.pl_title
ORDER BY pl.pl_title
LIMIT 5000;

SQL

Checking query status...