Suppose there is one table that consists of two fields: the material number and the status. One material can have 2 statuses (for example 1 and 2) or only 1 status. It is necessary to write a request that will unload from the database exactly those materials whose status is only 2. Ie, you need to display materials with status 2, excluding those materials where there is material with status 1. From the picture it follows that the expected answer is 1011

enter image description here

1 answer 1

You can try to do this:

with t(mat, status) as ( select 'A', 2 from dual union all select 'A', 1 from dual union all select 'B', 2 from dual union all select 'B', 2 from dual ) select distinct mat, status from t where mat not in (select mat from t where status = 1); 

Or option with not exists