-1

I'm working with 3 table. Driver, Car, and Garage. Garage is a 1 to N relationship table between Driver and Car (One driver has at least one car)

Driver Table
╔════╦══════╗
║ ID ║ Name ║
╠════╬══════╣
║  1 ║ Edson║
║  2 ║ Tomas║
║  3 ║ Carry║
╚════╩══════╩

Car Table
╔════╦════════╗
║ ID ║ Brand  ║
╠════╬════════╣
║  1 ║ Ferrari║
║  2 ║ Porche ║
║  3 ║ McLaren║
╚════╩════════╩
Garage Table
╔════╦═════════╗═══════
║ ID ║Driver.Id║Car.Id║
╠════╬═════════╣═══════
║  1 ║ 1       ║  1   ║
║  2 ║ 2       ║  2   ║
║  3 ║ 2       ║  3   ║
║  4 ║ 3       ║  2   ║
╚════╩═════════╩═══════

I need get a Drivers list with your cars. Something like this:

[Edson, [Ferrari]]
[Tomas, [Porche, McLaren]]
[Carry, [Porche ]]

I already tried the code below, but I didn't get the expected result.

connection.execute(db.select([Driver.Name, Car.Brand]).where(Garage.Driver.Id == Driver.ID and Garage.Car.Id == Car.ID)).fetchall()

So, how can I make JOIN query to get the expected results?

YatShan
  • 425
  • 2
  • 8
  • 22
Jefferson
  • 307
  • 1
  • 5
  • 12

1 Answers1

0

You can use group_concat as shown below.

data input:

#drop table if exists driver;
create table driver (id int(11), name varchar(255));
insert into driver (id, name) values (1, 'Edson');
insert into driver (id, name) values (2, 'Tomas');
insert into driver (id, name) values (3, 'Carry');

#drop table if exists car;
create table car (id int(11), brand varchar(255));
insert into car (id, brand) values (1, 'Ferrari');
insert into car (id, brand) values (2, 'Porsche');
insert into car (id, brand) values (3, 'McLaren');

#drop table if exists garage;
create table garage (id int(11), driver_id int(11), car_id int(11));
insert into garage (id, driver_id, car_id) values (1, 1, 1);
insert into garage (id, driver_id, car_id) values (2, 2, 2);
insert into garage (id, driver_id, car_id) values (3, 2, 3);
insert into garage (id, driver_id, car_id) values (4, 3, 2);

solution:

select d.name, group_concat(c.brand separator ', ')
from driver d
left join garage g on d.id = g.driver_id
left join car c on g.car_id = c.id
group by d.id;

You can also check here where the approach is dealt with in great lengths: Can I concatenate multiple MySQL rows into one field?

LeroyFromBerlin
  • 395
  • 3
  • 12