Multiple columns in a subquery

Question

I am trying to find the products selected in previous week vs products selected for this week to find the churn in selection decision. Currently I am doing it for a single site and the result works fine with the correct number of records. Now I want to change the code where I get the output for 10 sites in a single query.

create temporary view removes as
  select distinct
         asin,
         lagweek,
         fc,
         'removed' as state,
         demand_pp,
         instock_pp,
         source,
         filter_reason,
         is_missing_in_pp,
         is_missing_in_dc,
         is_missing_in_nmi,
         asin_nmi,
         asin_pre,
         asin_dc,
         filter_reason_old,
         asin_omi,
         asin_preo,
         asin_dco
  from sel_old so where asin not in (select asin from sel_new sn where sn.lagweek = so.lagweek);

Since this is for a single site just doing asin not in (select asin ...) works fine but now I want to look at ASINs across multiple sites from the same logic. I tried the approach below but it returns incorrect number of records.

create temporary view removes as
  select distinct
         so.asin,
         so.lagweek,
         so.fc,
         'removed' as state,
         so.demand_pp,
         so.instock_pp,
         so.source,
         so.filter_reason,
         so.is_missing_in_pp,
         so.is_missing_in_dc,
         so.is_missing_in_nmi,
         so.asin_nmi,
         so.asin_pre,
         so.asin_dc,
         so.filter_reason_old,
         so.asin_omi,
         so.asin_preo,
         so.asin_dco
  from sel_old so left join (select asin, fc, lagweek from sel_new) as sn
         on (so.asin <> sn.asin)
         and (so.fc = sn.fc)
         and (so.lagweek = sn.lagweek);

How should I approach this. I haven't been able find an easier solution if there are any.

score 0 · Answer 1 · answered Apr 09 '21 at 20:59

You can use EXISTS predicate. It doesn't produce additional records, just tests the existence of some case and filters accordingly.

select distinct
         so.asin,
         so.lagweek,
         so.fc,
         'removed' as state,
         so.demand_pp,
         so.instock_pp,
         so.source,
         so.filter_reason,
         so.is_missing_in_pp,
         so.is_missing_in_dc,
         so.is_missing_in_nmi,
         so.asin_nmi,
         so.asin_pre,
         so.asin_dc,
         so.filter_reason_old,
         so.asin_omi,
         so.asin_preo,
         so.asin_dco
from sel_old so
where not exists (
  select 1
  from sel_new sn
  where so.fc = sn.fc
    and so.lagweek = sn.lagweek
    and so.asin = sn.asin
)

Multiple columns in a subquery

1 Answers1