Selecting from a column matching two conditions

Databases
1

I’m not sure how to describe this, I know it is probably something real easy, but I didn’t know how to search for it.

Say I have two tables like this:

Vehicle
car_id - type
1 - car
2 - truck
3 - van
4 - motorcycle

colors
car_id - color
1 - red
1 - black
1 - blue
2 - red
2 - green
3 - white
3 - gray
4 - red
4 - green

I’m trying to find vehicles that come in two colors, not or, but both.

SELECT type FROM vehicle 
LEFT JOIN colors
USING (car_id)
WHERE colors.color = 'red' AND colors.color = 'green';

I see it doesn’t work because no row can have both those values. If I do an OR satement I get answers, but I should only be returning “truck” as an answer OR will give me car, van, motorcycle.

2 
SELECT vehicle.type 
  FROM colors
INNER
  JOIN vehicle 
    ON vehicle.car_id = colors.car_id
 WHERE colors.color IN ( 'red' , 'green' )
GROUP
    BY vehicle.type
HAVING COUNT(*) = 2

:slight_smile: