MySQL: how to select many results from linked table at one row to another?

Question

Hello. At first - Im sql newbie, sorry for this (mbe typicall) question.

I Have two table: table of organisations...

id_org    org_name
1         Organiz1
2         Organiz2

and table of organization staff.

id_staff  staff_name   id_org
1         John         1
2         Jack         1
3         Sally        1
4         Peter        1
5         Andy         2
6         Joe          2

I want sql answer(two rows) like this

1 Organiz1 1 John 2 Jack 3 Sally 4 Peter
2 Organiz2 5 Andy 6 Joe

and I want what each name or id of staff will be named as staff_1_name(staff_2_name,staff_3_name) and staff_1_id. How I can get it?

Answer 1

 SELECT o.id_org, o.org_name, GROUP_CONCAT(concat(s.id_staff, ' ', s.staff_name) ORDER BY s.id_staff SEPARATOR ' ')
 FROM Organizations o, staff s
 WHERE s.id_org = o.id_org
 GROUP BY id_org, org_name;

Answer 2

You're in luck. MySQL offers a handy function called GROUP_CONCAT() which you can use to build that result set:

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;

Test case:

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);

Result:

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

---

UPDATE:

@Micahel's solution also returns the same result. I recommend using that solution since you can concatenate your fields directly in the GROUP_CONCAT() function, instead of using a derived table:

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;