SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Mashi: Aug 15, 2005; Just wanted you to know your dinner's cold and the children all agree you're a shitheel!

I have a properties table where I store data relating to a particular row in key/value pairs. It looks like this:

Table: person_properties

code:

  id, person_id, property_name, property_value
  1   45         name           joe
  2   45         hair_color     brown
  3   45         likes_tuna     1
  4   46         name           sally
  5   46         hair_color     blonde
  6   46         likes_tuna     0

So as you can see I'm just storing data pertaining to persons in a vertical rather than horizontal format, so that I can hold very different data without altering any tables.

My question is: What is the cleanest / most efficient way to retrieve this data as if it were stored the regular horizontal way? The way I'm doing it right now is:

code:

SELECT p.*,
       (SELECT property_value
        FROM   person_properties pp
        WHERE  pp.person_id = p.id
               AND property_name = "name") AS name,
       (SELECT property_value
        FROM   person_properties pp
        WHERE  pp.person_id = p.id
               AND property_name = "hair_color") AS hair_color,
       (SELECT property_value
        FROM   person_properties pp
        WHERE  pp.person_id = p.id
               AND property_name = "likes_tuna") AS likes_tuna
FROM   people p
WHERE  id = 45

I'd like to do this using a single join but I can't find legal way to dynamically set column names. So is there a way, or is this the best I am going to get?

Mashi fucked around with this message at 00:45 on Dec 12, 2007

# ¿ Dec 11, 2007 22:57

Adbot: ADBOT LOVES YOU

# ¿ May 2, 2024 21:51

Mashi: Aug 15, 2005; Just wanted you to know your dinner's cold and the children all agree you're a shitheel!

Wow and there I was really proud of myself for thinking of such a great table structure :saddowns:

.

Thanks for the heads up, I'll reconsider it.

# ¿ Dec 12, 2007 03:09

Mashi: Aug 15, 2005; Just wanted you to know your dinner's cold and the children all agree you're a shitheel!

Questions moved to own thread.

Mashi fucked around with this message at 18:48 on Dec 16, 2007

# ¿ Dec 15, 2007 02:25

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'