CODEWITHSUNDEEP
sqloracle-database
Влад
Влад

Reputation: 25

select all data where all fields meet the condition

Please help me compose a sql query. There is a database (there are more columns in it, but I don’t think this is important). How to get all seller_id, where all stores are closed, if at least one store is open or temporarily closed, then it does not work. In the case of the data I gave below, only seller_id 1 and 4 should be displayed. Thanks in advance

seller_id | shop_id  | shop_status
-------------------------------------
    1     |   10     |   close
    2     |   11     |   open
    1     |   12     |   close
    2     |   13     |   temporarily_close
    3     |   14     |   open
    3     |   15     |   close
    4     |   16     |   close

Upvotes: 1

Views: 312

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use aggregation and a having clause:

select seller_id
from t
group by seller_id
having min(shop_status) = max(shop_status) and min(shop_status) = 'close';

If you have a separate list of sellers, then not exists might be faster:

select s.*
from sellers s
where not exists (select 1
                  from t
                  where t.seller_id = s.seller_id and
                        t.shop_status <> 'close'
                 );

EDIT:

If shop_status can be NULL and you want to exclude these, then you can use:

select seller_id
from t
group by seller_id
having min(shop_status) = max(shop_status) and
       min(shop_status) = 'close' and
       count(*) = count(shop_status);

And:

select s.*
from sellers s
where not exists (select 1
                  from t
                  where t.seller_id = s.seller_id and
                        (t.shop_status <> 'close' or t.shop_status is null)
                 );

Upvotes: 1

Related Questions