USE cswiki_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"

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 (=hlavní), Project (=Wikipedie), Category (=Kategorie)

    AND pl.pl_namespace = 0 # to main (=hlavní)

    # without links from pages from Project (=Wikipedie) namespace starting with

    AND (p.page_namespace <> 4

        OR (p.page_title NOT LIKE "Chybějící_stránky%"

            AND p.page_title NOT LIKE "Archiv_%")

        )

    AND pl.pl_title LIKE "Ol%" # 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;