tj86430 tj86430 - 1 month ago 8
MySQL Question

SQL: how to select all values of A that match with several B's (MySQL)

Let's say I have a table (called car_color) with two columns: car and color. The table represents which cars are available in which color, so if a Ford is available in green, the table will contain a row: car = 'Ford', color = 'green'. There are hundreds or thousands of cars and tens or hundreds of colors and thousands of combinations.

Then there is an application, where all the possible colors are listed, and the user can select one or more colors. We don't know how many the user will select. After the user has selected the colors, we want to list all the cars that are available in all the selected colors. What's the best way to do that in SQL? Let's say the user wants to find all the cars that are available in white, silver, blue, red and black?

I know this is probably simple, but somehow I can't find an elegant solution. If I wanted to list all the cars that are available in any of the above colors it would be:

select car from car_color where color in ('white', 'silver', 'blue', 'red', 'black')


but how to do it when I want only the cars available in all those colors?

Answer
select      car 
from        car_color 
where       color in ('white', 'silver', 'blue', 'red', 'black')
group by    car
having      count(*) = 5
;