Join Table With 2-Column Table, 1st Column Header, 2nd Column Value

Question

I have two tables as in the below diagram and I want to perform a join on them. The first table contains events, and the second table contains attributes for those events.

Schema

I want to write a query that will join the two tables below and create the output on the bottom.

Is this possible in MySQL? This question is very hard to google for (or even come up with a title for!), so I apologize if it is a duplicate.

Right now, I have this:

SELECT * FROM events e LEFT JOIN event_attributes ea ON e.id = ea.event_id WHERE e.id = 1;

Which produces 2 rows, one with time_opened and one with time_closed.

EDIT: I want this to be dynamic so I can add as many attributes as I want and the name becomes the header for the column and the value becomes the rows's value.

I'm confused by something in your example - Shouldn't the id on the events_attribute table be 1 in both for them to be attached to the event of id 1? — Sh4d0wsPlyr, Jun 03 '15 at 19:25
One method I used for accomplishing the same task was to simply specifiy which properties I want based on an ID. For example SELECT * FROM table WHERE id=X AND attribute_name="closed_time". Then simply join the queries together. Works great, but only if you know which attribute names you are looking for. — Sh4d0wsPlyr, Jun 03 '15 at 19:42

score 0 · Answer 1 · answered Jun 03 '15 at 19:26

0

You need 2 joins here:

SELECT * 
FROM events e 
LEFT JOIN event_attributes ea1 ON e.id = ea1.event_id 
                               and ea1.attribute_name = 'time_opened'
LEFT JOIN event_attributes ea2 ON e.id = ea2.event_id 
                               and ea2.attribute_name = 'time_closed'
WHERE e.id = 1;

answered Jun 03 '15 at 19:26

Giorgi Nakeuri

35,155
8
47
75

I edited my question to state that I want this to be dynamic. For example, if I were to add a third attribute, time_created, that would show up to. Is there a way to do that? – eliot Jun 03 '15 at 19:33

score 0 · Answer 2 · answered Jun 03 '15 at 19:27

0

Try this:

SELECT events.id AS id
,      events.description AS description
,      ea_open.value AS time_opened
,      ea_close.value AS time_closed

FROM   events

LEFT JOIN event_attributes AS ea_open
ON  ea_open.id = events.id
AND ea_open.attribute_name = 'time_opened'

LEFT JOIN event_attributes AS ea_close
ON ea_close.id = events.id
AND ea_close.attribute_name = 'time_closed';

answered Jun 03 '15 at 19:27

avk

871
1
9
22

I edited my question to state that I want this to be dynamic. For example, if I were to add a third attribute, time_created, that would show up to. Is there a way to do that? – eliot Jun 03 '15 at 19:33
Then have a look at http://stackoverflow.com/questions/14834290/mysql-query-to-dynamically-convert-rows-to-columns – avk Jun 03 '15 at 19:42

score 0 · Answer 3 · edited May 23 '17 at 11:51

0

You will have to change your tables around slightly, but you could do something along the lines of this to select the data from both tables and join it.

SELECT * FROM events, events_attributes WHERE events.id = event_attribute.id

Here is a link to a little more information on the subject: SELECT * FROM multiple tables. MySQL

edited May 23 '17 at 11:51

Community

1
1

answered Jun 03 '15 at 19:27

Sh4d0wsPlyr

948
12
28

1

This not giving the result eliot wants. – avk Jun 03 '15 at 19:28

Abhishekh Gupta · Answer 4 · 2015-06-03T19:45:25.340

0

You can try this:

SELECT 
  e.*,
  MAX(IF(ea.attribute_name = 'time_opened', ea.attribute_value, NULL)) AS time_opened,
  MAX(IF(ea.attribute_name = 'time_closed', ea.attribute_value, NULL)) AS time_closed
FROM events e 
  LEFT JOIN event_attributes ea ON e.id = ea.event_id 
WHERE e.id = 1
GROUP BY e.id, e.name, e.description;

edited Jun 03 '15 at 19:45

answered Jun 03 '15 at 19:39

Abhishekh Gupta

6,206
4
18
46

Join Table With 2-Column Table, 1st Column Header, 2nd Column Value

4 Answers4