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 
)
, TLDres as (
SELECT el_from, SUBSTRING_INDEX(SUBSTRING_INDEX(el_to_domain_index,'.',1),'/',-1) as TLD, el_to_domain_index, el_to_path
FROM externallinks 
WHERE  SUBSTRING_INDEX(el_to_domain_index,':',1) in ('http','https') and not exists( 1 from TLDlist where TLDlist.TLD=SUBSTRING_INDEX(SUBSTRING_INDEX(el_to_domain_index,'.',1),'/',-1))
)
SELECT * fro,
FROM TLDres 
limit 5

SQL

Checking query status...