Last week, while working for the CMS of my brother’s site, I was having problem in building an efficient query to fetch parents as well as their children in comma separated form from parent and child tables. Suppose I have a parent and child tables like shown below:

My problem was to fetch parent as well as corresponding children (comma separated) as shown below:

My first solution was to fetch all the parents first and for each parent I fetched their children and later changed those children in comma separated form. The problem with this solution is that I have to use multiple queries to get the desired data.

One of my friends suggested me to look for aggregate functions available. Voila!! I came up with this handy MySQL GROUP_BY extension – GROUP_CONCAT (expr) which proved to be the perfect solution to my problem. I got the desired data using single query shown below:
SELECT id, name, group_concat( childname ) FROM parent INNER JOIN child ON id = parent_id GROUP BY id
By default group_concat uses comma as a separator but the separator can be changed using SEPARATOR. The full syntax for GROUP_CONCAT is:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

For more information on My GROUP BY (Aggregation) functions,click here