StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You're in luck. MySQL offers a handy function called <a href="http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat" rel="nofollow noreferrer"><code>GROUP_CONCAT()</code></a> which you can use to build that result set:</p> <pre><code>SELECT o.id_org, o.org_name, GROUP_CONCAT(s.staff_name_id SEPARATOR ' ') FROM organisations o JOIN ( SELECT id_staff, id_org, CONCAT(id_staff, ' ', staff_name) staff_name_id FROM staff ) s ON (s.id_org = o.id_org) GROUP BY o.id_org, o.org_name; </code></pre> <p>Test case:</p> <pre><code>CREATE TABLE organisations (id_org int, org_name varchar(20)); CREATE TABLE staff (id_staff int, staff_name varchar(20), id_org int); INSERT INTO organisations VALUES (1, 'Organiz1'); INSERT INTO organisations VALUES (2, 'Organiz2'); INSERT INTO staff VALUES (1, 'John', 1); INSERT INTO staff VALUES (2, 'Jack', 1); INSERT INTO staff VALUES (3, 'Sally', 1); INSERT INTO staff VALUES (4, 'Peter', 1); INSERT INTO staff VALUES (5, 'Andy', 2); INSERT INTO staff VALUES (6, 'Joe', 2); </code></pre> <p>Result:</p> <pre><code>+--------+----------+---------------------------------------------+ | id_org | org_name | GROUP_CONCAT(s.staff_name_id SEPARATOR ' ') | +--------+----------+---------------------------------------------+ | 1 | Organiz1 | 1 John 2 Jack 3 Sally 4 Peter | | 2 | Organiz2 | 5 Andy 6 Joe | +--------+----------+---------------------------------------------+ 2 rows in set (0.00 sec) </code></pre> <hr> <p><strong>UPDATE:</strong></p> <p><a href="https://stackoverflow.com/questions/3821783/mysql-how-to-select-many-results-from-linked-table-at-one-row-to-another/3821821#3821821">@Micahel's solution</a> also returns the same result. I recommend using that solution since you can concatenate your fields directly in the <code>GROUP_CONCAT()</code> function, instead of using a derived table:</p> <pre><code>SELECT o.id_org, o.org_name, GROUP_CONCAT(CONCAT(id_staff, ' ', staff_name) SEPARATOR ' ') FROM organisations o JOIN staff s ON (s.id_org = o.id_org) GROUP BY o.id_org, o.org_name; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload