Join two tables on multiple columns and return only records from first table that are not in the second

Question

I have table 1 with data and table 2 as a lookup table with metadata. I need a query that returns all records from table 1 where either column in table 1 not exists in table two. The following query works fine for one column, but if I use more than one column in my join it´s not. working query:

select
T1.SCENARIO,
T1.year,
T1.period,
T1.ENTITY,
T1.account,
T1.ICP,
T1.MVMTS,
T1.C1,
T1.C2,
T1.C3,
T1.MULTI_GAAP,
T1.VLAUE

from "fccs".MV_FACT_FCCS T1 left outer join "fccs".V_META_NOT_FOUND V2 on trim(T1.account) = trim(V2.account)

where V2.account is null

not working correctly:

select
T1.SCENARIO,
T1.year,
T1.period,
T1.ENTITY,
T1.account,
T1.ICP,
T1.MVMTS,
T1.C1,
T1.C2,
T1.C3,
T1.MULTI_GAAP,
T1.VLAUE

from "fccs".MV_FACT_FCCS T1 left outer join "fccs".V_META_NOT_FOUND V2 on trim(T1.account) = trim(V2.account) and trim(T1.entity) = trim(v2.entity)

where V2.account is null or v2.entity is null

The second query continues to bring the records from table 1 where exist in table 2. Any help appreciated!

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-03-18T11:05:24

I need a query that returns all records from table 1 where either column in table 1 not exists in table two.

Use not exists:

select ff.*
from "fccs".MV_FACT_FCCS ff
where not exists (select 1
                  from "fccs".V_META_NOT_FOUND mnf
                  where trim(ff.account) = trim(mnf.account)
                 ) or
      not exists (select 1
                  from "fccs".V_META_NOT_FOUND mnf
                  where trim(ff.entity) = mnf.trimc
                 ) ;

Note: This answers the question as written. You might mean that both conditions are not met. In that case, use and instead of or.

Join two tables on multiple columns and return only records from first table that are not in the second

1 Answers