List of URL with invalid Top Level Domain

SQL

with TLDin 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 TLDin on  TLDh.TLD=TLDin.TLD
  WHERE TLDin.TLD is null
)
, p as (
  SELECT page.page_id, page.page_title 
  FROM page 
  WHERE page.page_namespace =0
) 
SELECT p.page_title
  ,  externallinks.el_to_path
  ,  externallinks.el_to_domain_index 
  ,  SUBSTRING_INDEX(SUBSTRING_INDEX(externallinks.el_to_domain_index,'.',1),'/',-1) as TLD
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...
