Join multiple MySQL tables

Question

I have 3 tables that I am trying to combine and get great results.

CREATE TABLE `car` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB mysql> select * from car; +----+-------+ | id | name | +----+-------+ | 1 | acura | +----+-------+ CREATE TABLE `tires` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `tire_desc` varchar(255) DEFAULT NULL, `car_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `new_fk_constraint` (`car_id`), CONSTRAINT `new_fk_constraint` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB mysql> select * from tires; +----+-------------+--------+ | id | tire_desc | car_id | +----+-------------+--------+ | 1 | front_right | 1 | | 2 | front_left | 1 | +----+-------------+--------+ CREATE TABLE `lights` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `lights_desc` varchar(255) NOT NULL, `car_id` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `new1_fk_constraint` (`car_id`), CONSTRAINT `new1_fk_constraint` FOREIGN KEY (`car_id`) REFERENCES `car` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB mysql> select * from lights; +----+-------------+--------+ | id | lights_desc | car_id | +----+-------------+--------+ | 1 | right_light | 1 | | 2 | left_light | 1 | +----+-------------+--------+ 

Here is my request.

 mysql> SELECT name, group_concat(tire_desc), group_concat(lights_desc) FROM car left join tires on car.id = tires.car_id left join lights on car.id = car_id group by car.id; +-------+-----------------------------------------------+-----------------------------------------------+ | name | group_concat(tire_desc) | group_concat(lights_desc) | +-------+-----------------------------------------------+-----------------------------------------------+ | acura | front_right,front_right,front_left,front_left | right_light,left_light,right_light,left_light | +-------+-----------------------------------------------+-----------------------------------------------+ 

I get duplicates, and this is what I would like to receive.

 +-------+-----------------------------------------------+--------------------------------+ | name | group_concat(tire_desc) | group_concat(lights_desc) | +-------+-----------------------------------------------+--------------------------------+ | acura | front_right,front_left | right_light,left_light | +-------+-----------------------------------------------+--------------------------------+ 

I can’t use different ones in group_concat because I can have legal duplicates that I would like to keep. Is there a way to make this request using joins rather than using internal selects like the instruction below?

 SELECT name, (select group_concat(tire_desc) from tires where car.id = tires.car_id), (select group_concat(lights_desc) from lights where car.id = lights.car_id) FROM car 

Also, if I use internal selects, will there be any performance issues over joins?

Answer 1

Is there a way to make this request using joins rather than using internal selects like the instruction below?

Exists, but not as efficiently and may omit some of the alleged duplicates:

 SELECT name, group_concat(DISTINCT tire_desc), group_concat(DISTINCT lights_desc) FROM car left join tires on car.id = tires.car_id left join lights on car.id = car_id group by car.id; 

Also, if I use internal selects, will there be any performance issues over joins?

In your case, yes: the sub-elections will be faster.

In MyISAM tables, in some cases, subselects tend to be faster than GROUP BY queries, due to the fact that GROUP BY in MyISAM is quite expensive due to failures in sorting / materialization:

• Aggregates: Subqueries vs GROUP BY

Answer 2

Perhaps you need to indicate which car_id you mean in the second ON

 SELECT name, group_concat(tire_desc), group_concat(lights_desc) FROM car left join tires on car.id = tires.car_id left join lights on car.id = **lights**.car_id group by car.id; 

I believe joins are faster than subqueries in InnoDB tables.