3

I have the following DB structure:

cars:

  • id
  • make

features:

  • id
  • name

cars_feature:

  • id
  • car_id
  • feature_id
  • value

I want to be able to select all cars with all features and return results with feature.name as the column name and cars_feature.value as the value. Right now I am able to get all feature and all values but I only figured out how to do that with group_concat.

What I am looking for is the following output:

car_id  car_make  color  wheels  doors
1       Ford      blue   alloy
2       Audi             alloy   3

Data example: SQLFiddle

2

With the information you gave, I think what you're trying to achieve is a Pivot table. I'd use CASE to make the pivot with your same query:

SELECT 
    cars.id as car_id, 
    cars.make as car_make, 
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color, 
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels, 
    IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
FROM cars
LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
LEFT JOIN features ON (car_feature.feature_id = features.id)
GROUP BY cars.id;

Test:

mysql> SELECT 
    -> cars.id as car_id, 
    ->     cars.make as car_make, 
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=1 THEN car_feature.value END),'') as color, 
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=2 THEN car_feature.value END),'') as Wheels, 
    ->     IFNULL(GROUP_CONCAT(CASE WHEN features.id=3 THEN car_feature.value END),'') as Doors
    -> FROM cars
    -> LEFT JOIN car_feature ON (cars.id = car_feature.car_id)
    -> LEFT JOIN features ON (car_feature.feature_id = features.id)
    -> GROUP BY cars.id;
+--------+----------+--------+--------+-------+
| car_id | car_make | color  | Wheels | Doors |
+--------+----------+--------+--------+-------+
|      1 | Ford     | Blue   | Alloy  |       |
|      2 | Audi     | Yellow |        | 4     |
+--------+----------+--------+--------+-------+
2 rows in set (0.00 sec)

mysql> 

Try it on this Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.