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