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:

parent_id name 1 parent1 2 parent2

parent_id childname 1 child1 1 child2 2 child3 2 child4

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

parent_id	name	children
1	parent1	child1,child2
2	parent2	child3,child4

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

Its been years i have been planning to blog but could not drop a line until this post. As it is well said that "better later than never", so here i am with my first blog post. Its good to externalize your thoughts, whims, preoccupations and blogging is meant for that. If information is an energy then blogging is all about conserving your energy as one cannot be sure of how long one can preserve information within him. Blogging is about mingling your thoughts in the vast ocean of others. Well i have started blogging, Have you?