MySQL Multiple Table Join

Question

I have a 3 tables that I'm trying to join and get distinct 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 query.

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 duplicate entires and this is what I would like to get.

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

I cannot use distinct in group_concat because I might have legitimate duplicates which I would like to keep. Is there any way to do this query using joins and not using inner selects like the statement 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 will use inner selects, will there be any performance issues over joins?

Answer 1

Is there any way to do this query using joins and not using inner selects like the statement below?

There is, but not as efficient and can omit some intended 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 will use inner selects, will there be any performance issues over joins?

In your very case, yes: the subselects will be faster.

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

• Aggregates: subqueries vs. GROUP BY

Answer 2

Maybe you have to specify 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 on InnoDB tables.