MySQL - Rows to Columns Join Statement problems

Question

I am trying to turn rows from one table into columns and fill each column with rows from another table.

The answer by Paul Dixon on Mar 16 '09 at 10:23 in this post appears to be very close to what I am trying to achieve. I posted the relavant part of his answer below. How to pivot a MySQL entity-attribute-value schema

SELECT file.*,
   attr1.value AS 'Attribute 1 Name', 
   attr2.value AS 'Attribute 2 Name', 
   ...
FROM
   file 
   LEFT JOIN attr AS attr1 
      ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
   LEFT JOIN attr AS attr2 
      ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
   ...

I have created a similar query to the example above:

SELECT
    forms.*,
    tbl_1.*,
    tbl_2.*,
    tbl_1.value as FirstName,
    tbl_2.value as LastName
FROM t_form_fields as forms
LEFT JOIN t_player_data as tbl_1
    ON(forms.id=tbl_1.form_field_id and tbl_1.form_field_id = 2)
LEFT JOIN t_player_data as tbl_2
    ON(forms.id=tbl_2.form_field_id and tbl_2.form_field_id = 3)

The current results of running this query are:

form_field_id   name        player_id   value   player_id   value   FirstName   LastName
2               First Name  1           Bob     NULL        NULL    Bob         NULL
2               First Name  2           Bill    NULL        NULL    Bill        NULL
2               First Name  3           Zech    NULL        NULL    Zech        NULL
2               First Name  4           Tim     NULL        NULL    Tim         NULL
3               Last Name   NULL        NULL    1           Goodman NULL        Goodman
3               Last Name   NULL        NULL    2           Herbst  NULL        Herbst
3               Last Name   NULL        NULL    3           CampbellNULL        Campbell
3               Last Name   NULL        NULL    4           Smith   NULL        Smith

The desired result is:

player_id   FirstName   LastName
1           Bob         Goodman
2           Bill        Herbst
3           Zech        Campbell
4           Tim         Smith

If you need more information please comment.

Sorry for my ignorance, I just realized that player_id isn't being added as shown in the example. I will rework it shortly. — zechdc, Nov 09 '11 at 00:56

AndreKR · Accepted Answer · 2011-11-09T01:07:18.510

1

SELECT file.*,
   MAX(attr1.value) AS 'Attribute 1 Name', 
   MAX(attr2.value) AS 'Attribute 2 Name', 
   ...
FROM
   file 
   LEFT JOIN attr AS attr1 
      ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
   LEFT JOIN attr AS attr2 
      ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
   ...
GROUP BY file.player_id

Are you expecting attributes to be missing? Otherwise you don't need the LEFT JOIN.

Another way of pivoting the table using only one join to the attribute table is:

SELECT file.*,
   MAX(IF(attr.AttributeId = 1, attr.value, NULL)) AS 'Attribute 1 Name', 
   MAX(IF(attr.AttributeId = 2, attr.value, NULL)) AS 'Attribute 2 Name', 
   ...
FROM
   file 
   LEFT JOIN attr ON(file.FileId=attr.FileId)
   ...
GROUP BY file.player_id

Please not that the IF() function syntax is MySQL-specific and needs to be rewritten to a CASE block for other DBMSs.

edited Nov 09 '11 at 01:07

answered Nov 09 '11 at 00:53

AndreKR

32,613
18
106
168

Sorry for my ignorance, I just realized that player_id isn't being added as shown in the example. I reworked the example to better show the issue I am having. – zechdc Nov 09 '11 at 01:08
The fields may be named differently but the principle still holds true. – AndreKR Nov 09 '11 at 01:11
When I tried your first suggestion of using `GROUP BY` it didn't give the end results I posted. I will try your second suggestion. – zechdc Nov 09 '11 at 01:14
The second solution you offered does exactly what I need. Thanks! – zechdc Nov 09 '11 at 03:21
Not really sure where to ask this. But what does `MAX` really do here. I have removed it and see how it changes, I have read the mysql documentation and it kinda makes sense, but I'm still confused to what its doing in the background... how its massaging the data the way I need it to? – zechdc Nov 09 '11 at 03:51
1

The `JOIN` gives you several lines of values, the `IF` filters away those you don't want (makes them NULL), so for `Attribute 1 Name` you get e.g. (NULL, "something", NULL, NULL). To `GROUP BY` the player_id those values have to be *aggregated* into one value. Thats what `MAX()` does, it picks the maximum value, and because *any* value is greater than NULL, it picks "something". – AndreKR Nov 09 '11 at 14:04

score 1 · Answer 2 · answered Nov 09 '11 at 02:28

1

Group by player_id (like I said in comments).

answered Nov 09 '11 at 02:28

Shomz

37,421
4
57
85

MySQL - Rows to Columns Join Statement problems

2 Answers2