mysql one-to-many anti join query

Question

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 1

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'
)