Select mysql data with where clause in 2 tables

Question

I have 2 mysql tables, as in the example below:

CARS

Id CAR NAME AGE 1 Ford 2 years 2 AUDI 1 years 3 Ford 2 years 

Functions

 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 that have ABS and ESP . Therefore, he should return in this example: 3 Ford

If you have an idea ...

thanks

Answer 1

A group having / will ensure that the vehicle has both necessary functions.

 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 

Answer 3

You need to use the join in the OPTIONS table:

 SELECT c.* FROM CARS c JOIN OPTIONS o ON o.id_car=c.id AND o.option='ABS' JOIN OPTIONS o2 ON o2.id_car=c.id AND o2.option='ESP' WHERE c.age >= 2 

Or subqueries:

 SELECT c.* FROM CARS c WHERE c.age >= 2 AND c.id IN ( SELECT o.id_car FROM OPTIONS o WHERE o.id_car=c.id AND o.option='ABS') AND c.id IN ( SELECT o.id_car FROM OPTIONS o WHERE o.id_car=c.id AND o.option='ESP') 

Answer 4

 SELECT * FROM CARS LEFT JOIN `OPTIONS` ON `OPTIONS`.`id_car` = `CARS`.`id` WHERE `OPTIONS`.`option` LIKE '%ABS%' 

Will do ABS, if you want more, you will need PHP to write SQL additions for each parameter, so that your final result is

 SELECT * FROM CARS LEFT JOIN `OPTIONS` ON `OPTIONS`.`id_car` = `CARS`.`id` WHERE (`OPTIONS`.`option` LIKE '%ABS%' OR `OPTIONS`.`option` LIKE '%ESP%') 

Answer 5

I suggest you change the column "age", or "date_created", or just leave it as age and use only an integer.

If you use as an integer, just do this:

 SELECT c.id, c.car FROM cars c INNER JOIN options o ON o.id_car = c.id WHERE c.age >= 2 

Answer 6

I also tried this way and returned to me both of ford 1 and 3 ....

I want only 3 Ford with abs and esp

Answer 7

Try:

  Select new_car.name From (select * from cars where year=2) as new_car inner join option as op on (op.id_car = new_car.id AND (op.option = `ABS` OR op.option = `ESP`)) 

:)