MySQL's GROUP_CONCAT

MySQL GROUP_CONCAT() is extremely useful for one-to-many relationships where performance is key.

Here’s a basic example of using GROUP_CONCAT() function:

SELECT GROUP_CONCAT(name) AS names
FROM members
WHERE (city = 'Ogden');

Which gives you:

names
Steve Thomas,Jon Stamos,Bill Nye

You can see that the names field is a comma-delimited list with all members from the city of Ogden.

Here’s a more advanced example using left joins in a one-to-many relationship:

SELECT
  m.id,
  m.name,
  GROUP_CONCAT(pn.number) AS numbers
FROM
  members m
LEFT JOIN
  phone_numbers pn ON (m.id = pn.member_id)
WHERE
  (m.city = 'Ogden')
GROUP BY m.id;

This is what you’ll get:

id name numbers
1 Steve Thomas 801-555-5555,801-555-5554,801-555-5553
2 Jon Stamos 801-555-5552,801-555-5551,801-555-5550
3 Bill Nye 801-555-5549,801-555-5548,801-555-5547

In this case, you have your member data along with a comma-delimited list of the given member’s phone numbers. This can potentially save you excess database requests in situations where you’ll need to pull relational data.

GROUP_CONCAT() also allows for a custom delimiter and an ORDER_BY clause as parameters.

SELECT
  m.id,
  m.name,
  GROUP_CONCAT(pn.number ORDER BY m.name DESC SEPARATOR '|') AS numbers
FROM
  members m
LEFT JOIN
  phone_numbers pn ON (m.id = pn.member_id)
WHERE
  (city = 'Ogden')
GROUP BY m.id;

Your output will show:

id name numbers
3 Bill Nye 801-555-5549|801-555-5548|801-555-5547
2 Jon Stamos 801-555-5552|801-555-5551|801-555-5550
1 Steve Thomas 801-555-5555|801-555-5554|801-555-5553


Congrats! You have your record set of members ordered by name, with all associated phone numbers in a custom delimited list.