Mysql joins and concatenate

Question

I have two tables:

table_a:            table_b
+----+------+    +----+---------+------+
| id | name |    | id | relation| name | 
+----+------+    ++++++---------+------+
| 1  | bob  |    | 1  | friend  | chris|
| 2  | jim  |    | 1  | friend  | jon  |
| 3  | tom  |    | 1  | brother | matt |
+----+------+    | 2  | friend  | sam  |
                 | 2  | parent  | ron  |
                 +----+---------+------+

and I want to enter a query to output something like

+----+------+------------+---------+--------+
| id | name |friend      | brother | parent |
+----+------+------------+---------+--------+
| 1  | bob  | chris, john| matt    |        |
| 2  | jim  | sam        |         |  ron   |
+----+------+------------+---------+--------+

So the id is the comman variable between the two tables, the relation variables have preset values (either friend, brother, parent and maybe a couple other types), and there can be multiple table_b.name per relation per id.

Is this too complicated a task to do?

Answer 1

SELECT a.ID, a.name,
        GROUP_CONCAT(CASE WHEN relation = 'friend' THEN b.name ELSE NULL END) friend,
        GROUP_CONCAT(CASE WHEN relation = 'brother' THEN b.name ELSE NULL END) brother,
        GROUP_CONCAT(CASE WHEN relation = 'parent' THEN b.name ELSE NULL END) parent
FROM  table_a a
        INNER JOIN table_b b
            ON a.id = b.id
GROUP BY a.ID, a.name

SQLFiddle Demo

in future, if you have any other relation other than friend, brother, and parent and you don't want to alter the query, you can use prepared statement

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'GROUP_CONCAT(CASE WHEN relation = ''',
      relation,
      ''' then b.name ELSE NULL end) AS ',
      relation
    )
  ) INTO @sql
FROM table_b;

SET @sql = CONCAT('SELECT a.ID, a.name, ', @sql, ' 
                   FROM  table_a a
                         INNER JOIN table_b b
                             ON a.id = b.id
                   GROUP BY a.ID, a.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQLFiddle Demo