PostgreSQL – Help comparing two tables against three specific columns

I need to match three columns across two tables. A query should select a row in t1 and search for any row in t2 where ALL three columns listed below match.

tbl_staged_documentation (t1 for reference)
orgname|name|subnet|customerid|customername|ipaddress|prefix

tbl_active_networks (t2 for reference)
orgid|ipaddress|prefixlength|sitename|siteid|name

Here are the three columns

t1.customerid = t2.orgid
t1.ipaddress = t2.ipaddress
t1.prefix = t2.prefixlength

I’ve looked into a JOIN and UNION. UNION looks like it can rip out duplicates, but I wasn’t able to get it. Nor the join.

Seems like one of these two options are the way to go but I’m not clear on how to do it.

select *
from tbl_staged_documentation t1
join tbl_active_networks t2
  on t1.customerid = t2.orgid
  and t1.ipaddress = t2.ipaddress
  and t1.prefix = t2.prefixlength
where
  t1.customerid = t2.orgid AND t1.ipaddress != t2.ipaddress AND t1.prefix != t2.prefixLength;

Also tried the following UNION

select customerid, ipaddress, prefix from tbl_staged_documentation
union
select orgid, ipaddress, prefixlength from tbl_active_networks;

Ultimately, I’m trying to find out what network information from t1 does not exist in t2. t1 is a source of truth. t2 contains data in a production system.

Data from t1 will dynamically be updated into t2, but due to heavy rate limiting on the system where t2’s data comes from I’m trying to clean it up before running the API calls.

Answer

I’m trying to find out what network information from t1 does not exist in t2

That’s precisely what the NOT EXISTS operator is for:

select *
from tbl_staged_documentation t1
where not exists (select *
                  from tbl_active_networks t2
                  where t1.customerid = t2.orgid
                    and t1.ipaddress = t2.ipaddress
                    and t1.prefix = t2.prefixlength)

Advertisement

Answer