List of URL with invalid Top Level Domain

SQL

with TLDlist as (
  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(e.el_to_domain_index,'.',1),'/',-1) as TLD
  FROM externallinks as e
  JOIN page ON page_id = el_from
  WHERE page.page_id=12869134 
)
, TLDh as (
  SELECT DISTINCT e.el_to_domain_index, SUBSTRING_INDEX(SUBSTRING_INDEX(e.el_to_domain_index,'.',1),'/',-1) as TLD
  FROM externallinks as e
  WHERE  SUBSTRING_INDEX(e.el_to_domain_index,':',1) in ('http','https') 
)
, TLDres as (
  SELECT TLDh.el_to_domain_index, TLDh.TLD
  FROM TLDh LEFT JOIN TLDlist on  TLDh.TLD=TLDlist=TLD
  WHERE TLDList.TLD is null)
, p as (
  SELECT page.page_id, page.page_title 
  FROM page 
  WHERE page.page_namespace =0
) 
SELECT p.page_title,TLDres.TLD TLDres.el_to_domain_index,externallinks.el_to_path
FROM  externallinks INNER JOIN p
  ON p.page_id = externallinks.el_from
INNER JOIN TLDres 
  ON externallinks.el_to_domain_index = TLDres.el_to_domain_index
SQL

Checking query status...
