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