0

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

user2568374
  • 1,164
  • 4
  • 11
  • 21
  • That does NOT work because it still returns Group ID 1 & 2 because it evaluates 'XXX' and 'YYY' as a NOT thus it returns Group IDs 1, 2 & 3. It fails because it evaluates each line in the items table separately, not as groups. If a Group has 'ZZZ' at all , it should NOT be included int he results. – user2568374 Oct 08 '19 at 12:10
  • 2
    Your code works fine: https://www.db-fiddle.com/f/uPys5WRWvaL1q5iyHW4Ze1/0 – forpas Oct 08 '19 at 21:00
  • Ok thanks, I thought it didn't work in another application. – user2568374 Oct 09 '19 at 17:16

0 Answers0