MySQL: check if a column has values based on another column

Question

I'm using PDO to handle my database and I have one table:

Table:
id    sku    category
1     XYZ    Ballerinas
2            Ballerinas
3            Ballerinas
4     ABC    Ballerinas

As you can see I have 4 rows that are in the same category Ballerinas, but two of them don't have an sku. How can I return those rows using a PDO query only if all Ballerinas have an sku?

Thank you! :D

those rows have null value or empty value? what's the default value for that column? — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Sep 17, 2018 at 7:08

Tim Biegeleisen · Accepted Answer · 2018-09-17 07:11:34Z

3

One option to find matching categories is to aggregate by category and then assert the total count matches the count of sku values which are not empty string. If these two counts match, then retain that category.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT category
    FROM yourTable
    GROUP BY category
    HAVING COUNT(*) = COUNT(CASE WHEN sku <> '' THEN 1 END)
) t2
    ON t1.category = t2.category;

Demo

answered Sep 17, 2018 at 7:11

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

emma Over a year ago

Hey @TimBiegeleisen, this is perfeeeecttttt, it does exactly what i need! :D Thank youuuuuuu! :D<3

emma Over a year ago

Yes @TimBiegeleisen, this answer solved my problem i just had to wait 10 minutes before i could mark it :D. Thank you again! :D

Carsten Massmann · Accepted Answer · 2018-09-17 07:43:34Z

2

Or you can do it without a join:

SELECT *
FROM Table1 t1
WHERE NOT EXISTS (SELECT 1 FROM Table1 t2 WHERE t1.category = t2.category AND t2.sku IS NULL);

If you consider empty values ('') also as "not being a value" then the condition in the exists clause should be

where t2.category=t1.category and not t2.SKU>''

edited Sep 17, 2018 at 7:43

answered Sep 17, 2018 at 7:15

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

2 Comments

Carsten Massmann Over a year ago

In the columns list of the outer query you don’t need to specify the table alias (t1.), since only one table is available for output.

emma Over a year ago

Thank you @cars10m, i've already used Tim's answer but this one works too :D

Collectives™ on Stack Overflow

MySQL: check if a column has values based on another column

2 Answers 2

Demo

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related