CODEWITHSUNDEEP
mysql
emma
emma

Reputation: 759

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

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

Upvotes: 1

Views: 3609

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28196

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>''

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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

Upvotes: 3

Related Questions