Select MySQL data with where clause in 2 tables

Question

I have 2 MySQL tables like in the example below:

CARS

Id     CAR NAME          AGE

1   Ford        2 years
2   AUDI        1 years 
3   Ford        2 years

OPTIONS

Id  id_car   option

1    1      ESP
2    2          ABS
3    3          ABS
4    3          ESP

And I need to select all cars of 2 years old which have ABS AND ESP. So it should return in this example: 3 Ford

Answer 1

The group by/having will ensure that the car has both of the desired features.

select c.id, c.name
    from cars c
        inner join options o
            on c.id = o.id_car
                and o.option in ('ABS','ESP')
    where c.age = 2
    group by c.id, c.name
    having count(distinct o.option) = 2

Answer 2

SELECT * FROM CARS WHERE id IN 
(SELECT id_car FROM OPTIONS WHERE GROUP_CONCAT(option) ='ABS,ESP' 
GROUP BY id_car) 
WHERE age ='2 years' GROUP BY CARS.name