MySQL: how to select many results from linked table at one row to another?
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?
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;
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;
精彩评论