MySQL inner join group_concat

Question

I have 4 MySQL Tables shown here:SQL Fiddle

I need to select station name, brand, fuel_type and fual_price for each station in given set and fuel typ in given set, e.q all Shell stations with fuel Diesel and Natural.

I would like to have all data related to one station in one row, which means I have e.g. 5 fuel types/prices for 1 station.

Example output -

id | name     | brand | fuel-data
---------------------------------------------------------
1  | Brzotice | Shell | Natural95-37.9, Diesel Power-45.8

Could you please help me?

score 3 · Accepted Answer · answered Oct 03 '12 at 18:38

I think this will give you want you want:

select s.id, 
  s.name,
  b.brand,
  group_concat(concat(f.type, '-', fs.price)) Fuel_Data
from sam_fuel f
left join sam_station_fuel fs
  on f.id = fs.id_fuel
left join sam_station s
  on fs.id_station = s.id
left join sam_station_brand b
  on s.brand = b.id
group by s.id, s.name, b.brand

See SQL Fiddle with Demo

Basically this query joins all of your tables. Then to get the fuel_data field it applies both the CONCAT() and the GROUP_CONCAT() functions

thank you, I did not know how to write group concat – samonchain Oct 03 '12 at 19:15 — samonchain, Oct 03 '12 at 19:15

MySQL inner join group_concat

1 Answers1