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')
select car from car_color where color in ('white', 'silver', 'blue', 'red', 'black') group by car having count(*) = 5 ;