Moshe Moshe - 25 days ago 9
MySQL Question

Concat on one-to-many when joining tables

I have these tables:

Hosts:

+----+------+---------+-------------+------------+--------+-----------------------+-----------------------+-------+
| id | name | url | environment | instanceId | region | created_at | updated_at | value |
+----+------+---------+-------------+------------+--------+-----------------------+-----------------------+-------+
| 4 | test | testing | qa | foo | bar | "2017-09-23 14:57:18" | "2017-09-23 14:57:18" | 1222 |
+----+------+---------+-------------+------------+--------+-----------------------+-----------------------+-------+


ddframes:

+----+-------+-------+
| id | value | owner |
+----+-------+-------+
| 1 | 1222 | 4 |
| 2 | 333 | 4 |
| 3 | 444 | 4 |
+----+-------+-------+


owner
on ddframes is a foreign key of
id
on
hosts


My desired output is:

+----+------+---------+-------------+------------+--------+-----------------------+-----------------------+--------------+
| id | name | url | environment | instanceId | region | created_at | updated_at | ddframes |
+----+------+---------+-------------+------------+--------+-----------------------+-----------------------+--------------+
| 4 | test | testing | qa | hiri | sdf | "2017-09-23 14:57:18" | "2017-09-23 14:57:18" | 1222,333,444 |
+----+------+---------+-------------+------------+--------+-----------------------+-----------------------+--------------+


Where the ddframes are concatenated together.

What I have is:

SELECT h.*, d.value as ddframes
FROM hosts h
INNER JOIN ddframes d
ON h.id = d.owner
GROUP BY h.id;


But it produces something similar to my desired output just without the other ddframes. Only the first one.

Is that achievable? Maybe my tables' design is wrong?

Answer Source

You want the group_concat() function:

SELECT h.*, GROUP_CONCAT(d.value) as ddframes
FROM hosts h INNER JOIN
     ddframes d
     ON h.id = d.owner
GROUP BY h.id;

I should note. In general, it is a bad idea to have SELECT * in a GROUP BY query. Assuming that hosts.id is unique (which the primary key is), then this is acceptable. In fact, the ANSI standard supports this construct for unique keys in the GROUP BY.