title: MySQL Multiple Table Join
body: <p>I have a 3 tables that I'm trying to join and get distinct results.</p>
  
  <pre><code>CREATE TABLE  `car` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB
  
  mysql&gt; 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&gt; 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&gt; select * from lights;
  
  +----+-------------+--------+
  | id | lights_desc | car_id |
  +----+-------------+--------+
  |  1 | right_light |      1 | 
  |  2 | left_light  |      1 | 
  +----+-------------+--------+
  </code></pre>
  
  <p>Here is my query.</p>
  
  <pre><code>mysql&gt; 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 | 
  +-------+-----------------------------------------------+-----------------------------------------------+
  </code></pre>
  
  <p>I get duplicate entires and this is what I would like to get.</p>
  
  <pre><code>+-------+-----------------------------------------------+--------------------------------+
  | name  | group_concat(tire_desc) | group_concat(lights_desc) |
  +-------+-----------------------------------------------+--------------------------------+
  | acura | front_right,front_left | right_light,left_light | 
  +-------+-----------------------------------------------+--------------------------------+
  </code></pre>
  
  <p>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?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Also, if I will use inner selects, will there be any performance issues over joins?</p>