How to use a proprety/value table in MySQL

Posted by David on Stack Overflow See other posts from Stack Overflow or by David
Published on 2010-06-09T02:16:58Z Indexed on 2010/06/09 2:22 UTC
Read the original article Hit count: 281

Filed under:

I inherited a mysql database that has a table with columns like this:

object_id, property, value

It holds data like this:

1,first_name,Jane
1,last_name,Doe
1,age,10
1,color,red
2,first_name,Mike
2,last_name,Smith
2,age,20
2,color,blue
3,first_name,John
3,last_name,Doe
3,age,20
3,color,red
...

Basically what I want to do is treat this table as a regular table. How would I get the id numbers (or all properties) of a person who is age 20 sorted by last and than first name? So far I have:

SELECT object_id FROM table WHERE property = 'age' AND value = '20'
union
SELECT object_id FROM table WHERE property = 'color' AND value = 'red'

But I'm not sure how to go about ordering the data.

Thanks

© Stack Overflow or respective owner

Related posts about mysql