CODEWITHSUNDEEP
mysqlpivotmultiple-columns
OnionHull
OnionHull

Reputation: 3

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

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.

Upvotes: 0

Views: 1566

Answers (1)

Jacky Cheng
Jacky Cheng

Reputation: 1556

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)

Upvotes: 2

Related Questions