MySQL query: How to get body concatenation from test1 with a space as a join to get "something1 something2"?

Question

tune:

mysql> create table main(id integer unsigned); mysql> create table test1(id integer unsigned,body text); Query OK, 0 rows affected (0.84 sec) mysql> insert into main(id) value(1); Query OK, 1 row affected (0.16 sec) mysql> insert into test1(id,body) value(1,'something1'); Query OK, 1 row affected (0.27 sec) mysql> insert into test1(id,body) value(1,'something2'); Query OK, 1 row affected (0.00 sec) 

Via

 mysql> select main.id,body from main -> left join test1 on main.id=test1.id -> group by main.id; 

returns:

 +------+------------+ | id | body | +------+------------+ | 1 | something1 | +------+------------+ 1 row in set (0.02 sec) 

How to get body concatenation from test1 with a space in the form of a connection to get "something1 something2"?

Answer 1

 SELECT main.id, GROUP_CONCAT(body SEPARATOR ' ') FROM main LEFT JOIN test1 on main.id=test1.id GROUP BY main.id 

See more details.