mysql one-to-many anti join query

Looking for help with a query for this situation:

I have 3 tables. Shoe table is a unique collection of shoe models. Color table is a unique collection of colors a shoe could have. shoe_color table is a join table. One shoe model can have multiple colors or just one.

I am wanting to query shoe models that do not contain a particular color. As an example … Shoe 1 has the colors black, red, white. Shoe 2 has the colors black, white. Shoe 3 has the colors black, white. The query for doesn’t contain the color red should return the rows: Shoe 2 and Shoe 3.

Any help is appreciated and please ask any questions for clarification!

Answer

You can use not exists.

Assuming that the join table refers to the other tables through their primary key (say color_id and shoe_id), and that column color_name in table color stores the color name, you would go:

select s.*
from shoe s
where not exists (
    select 1 
    from shoe_color sc 
    inner join color c on c.color_id = sc.color_id
    where sc.shoe_id = s.shoe_id and c.color_name = 'red'
)