List all values if one condition per criteria is met

user_4934150 0
Viewed 35

DB-Fiddle:

CREATE TABLE operations (
    id int auto_increment primary key,
    campaign VARCHAR(255),
    supplier VARCHAR(255),
    supplier_rating VARCHAR(255),
    quantity INT
);

INSERT INTO operations
(campaign, supplier, supplier_rating, quantity)
VALUES 
("C001", "Supplier_A", "high", "300"),
("C001", "Supplier_D", "high", "420"),
("C001", "Supplier_F", "low", "200"),

("C002", "Supplier_L", "high", "910"),
("C002", "Supplier_B", "mid", "370"),

("C003", "Supplier_Z", "mid", "136"),
("C003", "Supplier_M", "low", "247"),
("C003", "Supplier_C", "high", "496");

Expected Result:

     campaign    |     supplier      |     supplier_rating
-----------------|-------------------|-----------------------------
    C001         |    Supplier_A     |     high
    C001         |    Supplier_D     |     high
    C001         |    Supplier_F     |     low
-----------------|-------------------|-----------------------------
    C003         |    Supplier_Z     |     mid
    C003         |    Supplier_M     |     low
    C003         |    Supplier_C     |     high

In the above example I want to query all campaigns that contain a supplier with a supplier_rating with low.
All campaigns that fullfill this condition should be listed with their corresponding supplier and supplier_rating in the result.

I tried to go with this query:

SELECT 
campaign,
supplier,
supplier_rating,
(CASE WHEN MAX(supplier_rating = 'low') OVER (PARTITION BY campaign) = 1
THEN 'low' ELSE supplier_rating END) AS supplier_evaluation2
FROM operations;

It comes close to the result but instead of showing each supplier_rating per supplier it displays low for all suppliers per campaign and it also does not exclude C002.

How do I need to modify this query to achieve the expected result?

mysql sql
2 Answers

you can use exists

SELECT 
campaign,
supplier,
supplier_rating    
FROM operations t1    
WHERE exists  (SELECT 1 FROM operations t2 WHERE t1.campaign = t2.campaign
                        and t2.supplier_rating = 'low')
user_68085830

Isn't it just a where that is missing? Or am I missing something?

SELECT 
campaign,
supplier,
supplier_rating

FROM operations

WHERE campaign IN (SELECT campaign FROM operations WHERE supplier_rating = 'low')
user_19852040
Related