MySQL, how to JOIN many records to one

Question

I'm trying to get information from a wordpress database; the data I need is spread between 3 tables:

• wp_em_events
• wp_em_locations
• wp_postmeta

A simplified diagram:

wp_em_events
+-----+--------+-----+
| id  | loc_id | a   |
+-----+--------+-----+
|   1 | 3      | foo |
|   2 | 4      | bar |
+-----+--------+-----+

wp_em_locations
+--------+----+
| loc_id | b  |
+--------+----+
|    3   | x  |
|    4   | y  |
+--------+----+

wp_postmeta
+-------+------+------+
|post_id| key  |value |
+-------+------+------+
|    1  | name | dave |
|    1  | age  | 14   |
|    2  | name | rob  |
|    2  | age  | 20   |
+-------+------+------+

$querystr = "
    SELECT *
    FROM wp_em_events
    LEFT JOIN wp_em_locations ON wp_em_events.loc_id = wp_em_locations.loc_id
    LEFT JOIN wp_postmeta ON wp_em_events.id = wp_postmeta.post_id
    WHERE wp_em_events.id = 1
    GROUP BY wp_em_events.location_id, wp_em_events.id
";

This would return:

+-----+--------+-----+----+-----+-------+
| id  | loc_id | a   |  b | key | value |
+-----+--------+-----+----+-----+-------+
|   1 | 3      | foo |  x | age |  20   |
+-----+--------+-----+----+-----+-------+

Where my GROUP BY has destroyed the name->dave I would like to receive:

+-----+--------+-----+----+-----+-----+
| id  | loc_id | a   |  b | name| age |
+-----+--------+-----+----+-----+-----+
|   1 | 3      | foo |  x | dave|  14 |
+-----+--------+-----+----+-----+-----+

I need to capture ALL of the meta records, and associate them somehow with the parent record, preferably as the key->value that they are.

Answer 1

Well you could do this..

SELECT e.id, 
       e.loc_id, 
       e.a, 
       l.b, 
       p_name.Value `Name`, 
       p_age.Value  Age 
FROM   wp_em_events e 
       INNER JOIN wp_em_locations l 
         ON e.loc_id = l.loc_id 
       INNER JOIN wp_postmeta p_name 
         ON e.id = p_name.Post_id 

       INNER JOIN wp_postmeta p_age 
         ON e.id = p_age.Post_id 

WHERE  e.id = 1 
            AND p_name.`key` = 'name' 
            AND p_age.`key` = 'age' 

DEMO

But you'd need to construct the query dynamically. Specifically the column names and aliases and the WHERE clause

Answer 2

Do a dynamic pivot table. here You got a example that I've been using for one of my projects. It is not directly connected with Your case but You will get the point.

create table testowy_pivot
            (
             name varchar(255),
             color varchar(255),
             quantity int
            )

insert into testowy_pivot (name, color, quantity) values ('road frames','black',null)
insert into testowy_pivot (name, color, quantity) values ('road frames','red',null)
insert into testowy_pivot (name, color, quantity) values ('helmets','red',288)
insert into testowy_pivot (name, color, quantity) values ('helmets','black',324)
insert into testowy_pivot (name, color, quantity) values ('helmets','blue',216)
insert into testowy_pivot (name, color, quantity) values ('socks','white',180)
insert into testowy_pivot (name, color, quantity) values ('socks','white',216)


DECLARE @columns VARCHAR(8000)

SELECT
@columns = COALESCE(@columns + ',[' + cast(color as varchar) + ']','[' + cast(color as varchar)+ ']')
FROM testowy_pivot
GROUP BY color


DECLARE @query VARCHAR(8000)

SET @query = '
           SELECT *
                     FROM testowy_pivot
                               PIVOT
                                  (
                           sum(quantity) --sum, max, etc.
                               FOR [color]
                               IN (' + @columns + ')
                                  )
                     AS p'

EXECUTE(@query)

select * from testowy_pivot

regards, m.