sqloracle-database

SQL to find missing records using NotExists

Wondering if anyone can assist with below SQL in Oracle. In my table, I have items setup at both WHs and DCs as 1-1 relations and my table looks like below.

Table

I'm trying to write an SQL where I need to locate those items where the Item-Warehouse-DC record exists but a specific Item-DC relationship itself does not exist.

In below eg, Im trying to find Items that are missing setup with DC1 i.e. I'm trying to get Item5 and Item6

Expected result

I tried with this but no luck.

select Item
from SKU
WHERE Warehouse in ('WH-A','WH-B','WH-C') 
and DC = 'DC1'
and not exists (select * from SKU where DC = 'DC1';

Thanks for any advice or input. My SQL is not very strong. thank you again.

Solution

  • From what I understand, what you want is something like this.

    select Item
from SKU s
WHERE Warehouse in ('WH-A','WH-B','WH-C') 
and DC = 'DC1'
and not exists (
    select 1
    from SKU 
    where Warehouse = s.DC and Item = s.Item
);