MySql filtering and grouping.

Question

I am having trouble filtering data out a relational table. The query is part of a join, but I am stuck on a basic part.

I need to remove all the results with a certain id if the condition is found.

query similar to:

select * from colors where color != 'red' group by id

id  color
1   red
1   blue
1   blue
2   green
2   blue
3   green
3   orange
4    red
5    white

returns 1,2,3,5

I need it to only return 2,3,5

I am not sure what mysql command to use

Answer 1

select * from colors group by id having not group_concat(color) LIKE "%red%"

should work (not tested).

But it's weird to have many ids with same value...

Answer 2

SELECT DISTINCT id
FROM my_table
WHERE id NOT IN (
   SELECT id
   FROM my_table
   WHERE color = ?
)