Mysql integrates and integrates

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 | +----+------+------------+---------+--------+ 

Thus, id is a comman variable between two tables, relationship variables have given values ​​(friend, brother, parent, and possibly a couple of other types), and there may be several table_b.name for each relationship for each identifier.

Is this too much of a challenge?

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 the future, if you have a different relationship than friend, brother, and parent , and you do not want to change the request, you can use the 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