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;
 
         加载中,请稍侯......
 加载中,请稍侯......
      
精彩评论