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

​

​

​