CODEWITHSUNDEEP
mysqlsql
x-code
x-code

Reputation: 608

Exclude group if at least one row within it matches the condition

This is my sample table :

-----------------------------------
    name    | price |    status   
------------+-------+--------------
 apple      | 50.00 |
 apricot    | 30.00 |
 cherry     | 32.00 |
 orange     | 25.00 |
 pine-apple | 35.00 |
 orange     | 20.00 | out-of-stock
 apricot    | 35.00 | out-of-stock
 grapes     | 30.00 | out-of-stock

I need to retrieve records where price is less than 40 and exclude records where for the same name status value is out-of-stock at least once.

The desired result is represented below:

------------
    name
------------
 cherry      
 pine-apple

SQLFiddle

Upvotes: 2

Views: 1260

Answers (2)

potashin
potashin

Reputation: 44581

You can use LEFT JOIN and IS NULL check :

SELECT t1.* 
FROM fruits t1 
LEFT JOIN ( SELECT name 
            FROM fruits
            WHERE status = 'out-of-stock') t2 ON t1.name = t2.name
WHERE t1.price < 40
  AND t2.name IS NULL

SQLFiddle

Or IN

SELECT t1.* 
FROM fruits t1 
WHERE t1.price < 40 
  AND t1.name NOT IN ( SELECT name
                       FROM fruits
                       WHERE status = 'out-of-stock')

SQLFiddle

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Use NOT EXISTS to avoid sold out fruits.

select name
from fruits f1
where price < 40.00
and not exists (select * from fruits f2
                where f1.name = f2.name
                  and status = 'out-of-stock')

Upvotes: 6

Related Questions