Using field values from joined query as columns

Question

I have two MySQL tables describing data that can be extended into subclasses, one that describes the parent class data and another one describing metadata fields as one-to-many relationships.

Example rows from table page:

id   | name
-----+-----------------------
123  | Example page
999  | Another page

Example rows from table page_metadata:

page_id | key        | value
--------+------------+----------------
123     | picture    | test.jpg
123     | video      | example.avi
123     | sound      | sound.mp3
999     | picture    | something.jpg

Question: Is there some way to fetch all this data in this format?

id   | name             | picture       | video       | sound
-----+------------------+---------------+-------------+------------------
123  | Example page     | test.jpg      | example.avi | sound.mp3
999  | Another page     | something.jpg | NULL        | NULL

Johan · Accepted Answer · 2011-06-09 11:12:32Z

1

SELECT pm.page_id as id
  , p.name
  , max(IF(pm.key = 'picture', pm.value, null)) as picture
  , max(IF(pm.key = 'video', pm.value, null)) as video
  , max(IF(pm.key = 'sound', pm.value, null)) as sound
FROM page p
INNER JOIN page_metadata pm ON (p.id = pm.page_id)
GROUP BY p.id

answered Jun 9, 2011 at 11:12

Johan

77.4k28 gold badges204 silver badges346 bronze badges

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

1 Comment

Kaivosukeltaja Over a year ago

Yes, this is quite sufficient. I only changed INNER to LEFT to also include the pages that don't have any metadata attached to them. Thanks!

VAShhh · Accepted Answer · 2011-06-09 11:26:30Z

0

Try

SELECT page.id, page.name, picture.value, video.value, sound.value
FROM page
LEFT OUTER JOIN (SELECT page_id, value FROM page_metadata WHERE key = 'picture') as picture
  ON page.id = picture.page_id
LEFT OUTER JOIN (SELECT page_id, value FROM page_metadata WHERE key = 'video') as video
  ON page.id = video.page_id
LEFT OUTER JOIN (SELECT page_id, value FROM page_metadata WHERE key = 'sound') as sound
  ON page.id = sound.page_id

should get what you need

edited Jun 9, 2011 at 11:26

answered Jun 9, 2011 at 11:15

VAShhh

3,5142 gold badges26 silver badges40 bronze badges

Collectives™ on Stack Overflow

Using field values from joined query as columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related