Query: dns_parent_ns_record

with domain_list as (
  select
    distinct domain,
    substring(
      domain
      from
        '^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)'
    ) as tld
  from
    net_dns_record
  where
    domain = $1
),
domain_parent_server as (
  select
    l.domain,
    d.domain as tld,
    d.target as parent_server
  from
    net_dns_record as d
    inner join domain_list as l on d.domain = l.tld
  where
    d.type = 'SOA'
),
domain_parent_server_ip as (
  select
    *
  from
    net_dns_record
  where
    domain in (
      select
        parent_server
      from
        domain_parent_server
    )
),
domain_parent_server_with_ip as (
  select
    domain_parent_server.domain,
    domain_parent_server.parent_server,
    host(domain_parent_server_ip.ip) as ip_text
  from
    domain_parent_server
    inner join domain_parent_server_ip on domain_parent_server.parent_server = domain_parent_server_ip.domain
  where
    domain_parent_server_ip.type = 'A'
  order by
    domain_parent_server.domain
),
domain_parent_server_ns_list as (
  select
    net_dns_record.domain,
    domain_parent_server_with_ip.parent_server,
    net_dns_record.target
  from
    net_dns_record
    inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain
    and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text
    and net_dns_record.type = 'NS'
  order by
    net_dns_record.domain
),
ns_ips as (
  select
    domain,
    type,
    ip
  from
    net_dns_record
  where
    domain in (
      select
        target
      from
        domain_parent_server_ns_list
    )
    and type = 'A'
  order by
    domain
)
select
  domain_parent_server_ns_list.domain as "Domain",
  domain_parent_server_ns_list.parent_server as "Parent Server",
  domain_parent_server_ns_list.target as "Name Server",
  ns_ips.ip as "IP Address"
from
  domain_parent_server_ns_list
  left join ns_ips on domain_parent_server_ns_list.target = ns_ips.domain
order by
  domain_parent_server_ns_list.target;