CODEWITHSUNDEEP
mysqlsql
Moshe
Moshe

Reputation: 5139

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?

Upvotes: 0

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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.

Upvotes: 4

Related Questions