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

SQL

USE dewiki_p;
SELECT CONCAT('[[',GROUP_CONCAT(pr.page_title SEPARATOR ']], [['),']]') as foundpage, farchivepage  , el_to 
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, CONCAT('[[',GROUP_CONCAT(pf.page_title SEPARATOR ']], [['),']]') as farchivepage, max(euf.el_id) as m_id
        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 
GROUP BY el_to
ORDER BY m_id DESC

SQL

Checking query status...