mysql cross join not in?

Using a query like below you can fetch rows in which the color of the id is blue, purple, green, white, and black.

SELECT t1.id, col
FROM extra as e INNER JOIN your_table as t1 USING ( id )
CROSS JOIN your_table as t2 USING ( id )
CROSS JOIN your_table as t3 USING ( id )
CROSS JOIN your_table as t4 USING ( id )
CROSS JOIN your_table as t5 USING ( id )
WHERE t1.color = 'blue' and t2.color = 'purple' and t3.color= 'green' and t4.color= 'white' and t5.color= 'black'

If you try to use != or NOT IN, it doesn't seem to work. How would I write the query so that the colors would contain blue, purple, green, white, but NOT black?

You could do something along the lines of:

select e.id
from   extra as e
where  exists (select null from your_table as t where t.id = e.id and t.color = 'blue')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'purple')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'green')
  and  exists (select null from your_table as t where t.id = e.id and t.color = 'white')
  and not exists (select null from your_table as t where t.id = e.id and t.color = 'black')

Or, something like this would probably be more efficient:

select e.id
from   extra as e
where  4 = 
       (select count(*) 
        from   your_table as t 
        where  t.id = e.id 
          and  t.color in ('blue', 'purple', 'green', 'white'))
  and  0 = 
       (select count(*) 
        from   your_table as t 
        where  t.id = e.id 
          and  t.color in ('black'))