There are situations in which we would like to group values of multiple rows in just one field. Consider this little example in which we have three tables (cars, colors and link table) and we would like to have a query with all the cars in the first column, and all the colors in which tey are available in the second column.

Cars

IDCar Name
1 Audi
2 Mercedes
3 Bmw

 

Colors

IDColor Color
1 White
2 Blue
3 Black
4 Orange

 

Link Table

IDCar IDColor
1 1
1 2
1 4
3 2
3 4
2 3
3 1
1 3

 

The final table we want to obtain is something like this:

IDCar CarName AvailableColors
1 Audi Black, Blue, Orange, White
3 BMW Blue, Orange, White
2 Mercedes Black

 

To achieve this result, you can use a SQL query with the GROUP_CONCAT mysql function:

SELECT 
        `c`.`IDCar` AS `IDCar`,
        `c`.`Name` AS `CarName`,
        GROUP_CONCAT(`cl`.`Color`
            SEPARATOR ',') AS `AvailableColors`
    FROM
        ((`cars` `c`
        JOIN `linktable` `lt` ON ((`c`.`IDCar` = `lt`.`IDCar`)))
        JOIN `colors` `cl` ON ((`lt`.`IDColor` = `cl`.`IDColor`)))

in this way you will obtain an unordered list.

If you want:

  • Order the list by cars’ name, you will have to add both GROUP BY and ORDER BY clauses to the query:
...
FROM
        ((`cars` `c`
        JOIN `linktable` `lt` ON ((`c`.`IDCar` = `lt`.`IDCar`)))
        JOIN `colors` `cl` ON ((`lt`.`IDColor` = `cl`.`IDColor`)))
GROUP BY `c`.`Name`
ORDER BY `c`.`Name`
  • Order the colors in the AvailableColors Field, you will have to add the ORDER BY clause in the GROUP_CONCAT function:
 GROUP_CONCAT(`cl`.`Color`
            ORDER BY `cl`.`Color` ASC
            SEPARATOR ',') AS `AvailableColors`
  • Select only one occurence of color, in case of multiple occurencies, you will have to add the DISTINCT clause in the GROUP_CONCAT function:
GROUP_CONCAT(DISTINCT `cl`.`Color`
            SEPARATOR ',') AS `AvailableColors`

Bibliography