MySQL select multiple rows with multiple columns from other table in one row

Question

I'm looking for a way to structure given tables:

TABLE: group                    TABLE: person
-------------------------       -------------------------------------------------
| Group_id | Group_name |      | Person_id | Person_name | Person_fid | Group_id |
-------------------------       -------------------------------------------------
|    1     | Group44    |      |     1     |     John    |      2     |    1     |
-------------------------       -------------------------------------------------
|    2     | Best Group |      |     2     |    George   |      1     |    1     |
-------------------------       -------------------------------------------------
                               |     3     |    Peter    |      2     |    2     |

(Table person has additional columns like person_nickname, person_status etc.)

Into a Result like this: (Groupname, person_fid1 columns, person_fid2 columns)

 -------------------------------------------------------------------
|  Groupname   | fid1_pname | fid 1_pstat | fid2_pname | fid2_pstat |
 -------------------------------------------------------------------
|   Group44    |   George   |      1      |    John    |      0     |
 -------------------------------------------------------------------
|  Best Group  |   NULL     |   NULL      |    Peter   |      1     |
 -------------------------------------------------------------------

There will be 6 different Person_fids which I need to generate columns out of.

If a Group doesn't have a person with a certain fid it can show NULL for those columns. Is there a way to do this?

I've tried using:

SELECT group.name, MAX(CASE WHEN person.Person_fid = 1 THEN |get column infos|
ELSE NULL, NULL, NULL END) 3 columns fid1, 
MAX(CASE WHEN person.Person_fid = 2 THEN |get column infos|
ELSE NULL, NULL, NULL END) 3 columns fid2

But I couldn't get very far as I have no idea how to even search for this kind of stuff.

group is a reserved word. In consequence, it's a poor choice for a table/column identifier. That said, why do you want to do this? — Strawberry
– Strawberry, Commented Aug 8, 2014 at 7:38
fid 1_pstat which columns values? and do you want to show 6 person in single row — Sathish
– Sathish, Commented Aug 8, 2014 at 7:58
@Strawberry I know that this is quite weird, but the whole structure wasn't initially built for something like this. (Multiple persons) — OnionHull
– OnionHull, Commented Aug 8, 2014 at 9:28
@Sathish Yes 6 persons with their info in one column, but I already got everything working (thanks to Jacky Chengs answer). — OnionHull
– OnionHull, Commented Aug 8, 2014 at 9:35
@OnionHull Actually, you were on the right track for a more elegant solution than Jacky's - although I suspect that the accepted solution if fractionally faster than your alternative. Also, just FYI, in modern English "Thanks a bunch" has become an idiom for "That was very unhelpful" !! — Strawberry
– Strawberry, Commented Aug 8, 2014 at 12:04

Jacky Cheng · Accepted Answer · 2014-08-08 08:47:06Z

2

First change your table Group to something else like Group_info,person_group e.t.c. as group is a reserved word (it has some funionality on its own).

and the sql you need is probably as below (not tested, but should work)

SELECT 
    group_name, 
    t1.person_name as name_1, t1.person_status as stat_1,
    t2.person_name as name_2, t2.person_status as stat_2,
    t3.person_name as name_3, t3.person_status as stat_3,
    t4.person_name as name_4, t4.person_status as stat_4,
    t5.person_name as name_5, t5.person_status as stat_5,
    t6.person_name as name_6, t6.person_status as stat_6,
FROM group_info gi
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=1) as t1 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=2) as t2 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=3) as t3 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=4) as t4 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=5) as t5 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=6) as t6 USING (group_id)

answered Aug 8, 2014 at 8:47

Jacky Cheng

1,5561 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OnionHull Over a year ago

Thanks a BUNCH! I got it working. (Thanks for the reserved word info by the way)

Collectives™ on Stack Overflow

MySQL select multiple rows with multiple columns from other table in one row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related