Skip to Main Content
  • Questions
  • Find sku_no values from the table which does not have any records for ven_type='P'

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Dhruv.

Asked: January 20, 2021 - 4:31 pm UTC

Last updated: January 21, 2021 - 2:14 pm UTC

Version: Version 18.2.0.183

Viewed 1000+ times

You Asked

Hi Tom and Team,

I have a very simple table with the following records.

site_no,
sku_no,
vendor_id,
ven_type

A sku_no can have multiple vendor_id values associated to it. A vendor_id can have a ven_type = P (Primary) or S (Secondary)
I want to find all those sku_no values from the table that do not have any ven_type='P' records.

Kindly help me write a query for this.
Please let me know if the explanation is not clear or if you want me to provide any other details. Thank you.

with LiveSQL Test Case:

and Chris said...

You can do this with NOT EXISTS. In the subquery search for matching SKUs of type P:

create table vendor_item ( 
  site_no number(10) not null, 
  sku_no varchar2(50) not null, 
  vendor_id varchar2(50) not null, 
  ven_type varchar2(1) not null 
)
/

insert into vendor_item  
values  
(5121, 11172275, 'OWAJ', 'P')
/
insert into vendor_item  
values  
(5121, 11519912, 'OWAJ', 'P')
/
insert into vendor_item  
values  
(5121, 11445716, 'OWAJ', 'P')
/
insert into vendor_item  
values  
(5121, 11172275, '41000010J', 'S')
/
insert into vendor_item  
values  
(5121, 11519912, '41000010J', 'S')
/
insert into vendor_item  
values  
(5121, 11445716, '41000020J', 'S')
/
insert into vendor_item  
values  
(5121, 11171614, '41033300J', 'P')
/
insert into vendor_item  
values  
(5121, 11171614, 'OWAJ', 'S')
/
insert into vendor_item  
values  
(5121, 11171618, '41033300J', 'P')
/
insert into vendor_item  
values  
(5121, 11171618, 'OWAJ', 'S')
/
insert into vendor_item  
values  
(5121, 11170263, '41000020J', 'S')
/
insert into vendor_item  
values  
(5121, 11170263, '41000010J', 'S')
/
insert into vendor_item  
values  
(5121, 11170263, 'OWAJ', 'S')
/
insert into vendor_item  
values  
(5121, 10197056, '41000050J', 'S')
/
insert into vendor_item  
values  
(5121, 10197056, 'OWAJ', 'S')
/
insert into vendor_item  
values  
(5121, 11520369, '41002350J', 'S')
/
insert into vendor_item  
values  
(5121, 11448384, '41004350J', 'S')
/
commit
/

select sku_no, ven_type 
from   vendor_item;

select sku_no, ven_type 
from   vendor_item v
where  not exists (
  select * from vendor_item p
  where  p.sku_no = v.sku_no
  and    p.ven_type = 'P'
);

SKU_NO      VEN_TYPE   
10197056    S           
11520369    S           
11170263    S           
11170263    S           
11170263    S           
11448384    S  


This reads the table twice. It's usually faster to access a table once in a query.

You can do this by:

- Counting the P rows for each SKU in a window function
- Returning the rows where this count is zero

with rws as (
  select sku_no,   
         count ( 
           case when ven_type = 'P' then 1 end 
         ) over (
           partition by sku_no
         ) c
  from   vendor_item
)
  select * from rws
  where  c = 0;
  
SKU_NO     C   
10197056       0 
11170263       0 
11170263       0 
11170263       0 
11448384       0 
11520369       0


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.