I need to return groups where Items.value DOES NOT equal ZZZ in that group at all. I tried joining Items on group where items.value <> = 'ZZZ' but it still returns goups that have items with ZZZ. I also tried adding a sub select where selecting items.value = 'ZZZ' not exists but that didn't work either.
Groups
+----+------+--------------+
| id | name | TAG |
+----+------+--------------+
| 1 | AAA | 1 |
+----+------+--------------+
| 2 | BBB | 1 |
+----+------+--------------+
| 3 | CCC | 1 |
+----+------+--------------+
Items
+----+------+--------------+
| id | Value| Group_id |
+----+------+--------------+
| 1 | XXX | 1 |
+----+------+--------------+
| 2 | YYY | 1 |
+----+------+--------------+
| 3 | ZZZ | 1 |
+----+------+--------------+
| 4 | XXX | 2 |
+----+------+--------------+
| 5 | YYY | 2 |
+----+------+--------------+
| 6 | ZZZ | 2 |
+----+------+--------------+
| 7 | XXX | 3 |
+----+------+--------------+
| 8 | YYY | 3 |
+----+------+--------------+
| 9 | OOO | 3 |
+----+------+--------------+
Query on above should return
Group
+----+------+--------------+
| 3 | CCC | 1 |
+----+------+--------------+
This does not work. It returns Groups id 1, 2, and 3.
SELECT * FROM Groups g
WHERE NOT EXISTS (
SELECT 1 FROM Items as i
WHERE i.group_id = g.id
AND i.value = 'ZZZ'
);
How do you select all the groups above that have 'ZZZ' in Items and then determine which group_id were excluded? -> 3