1

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

informatik01
  • 16,038
  • 10
  • 74
  • 104
emma
  • 761
  • 5
  • 20

2 Answers2

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hey @TimBiegeleisen, this is perfeeeecttttt, it does exactly what i need! :D Thank youuuuuuu! :D<3 – emma Sep 17 '18 at 07:23
  • Yes @TimBiegeleisen, this answer solved my problem i just had to wait 10 minutes before i could mark it :D. Thank you again! :D – emma Sep 17 '18 at 07:32
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>''
Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43