dewiki: Find not archived URLs where the same URL is archived on same or an other article.

SQL

USE dewiki_p;
SELECT page_title, page_id, el_to, el_id , farchivepage
FROM externallinks as eur 
   INNER JOIN page as pr on eur.el_from=pr.page_id
   INNER JOIN (SELECT substring(euf.el_to,29+15) as f, min(pf.page_title) as farchivepage
        FROM externallinks as euf INNER JOIN page as pf on euf.el_from=pf.page_id
        WHERE el_index LIKE "https://org.archive.web./web/______________/%" 
        and pf.page_namespace=0
        GROUP BY substring(euf.el_to,29+15)      ) as filter on filter.f=el_to

WHERE pr.page_namespace=0 
LIMIT 100

SQL

Checking query status...