Need help with SQL Query

Question

Say I have 2 tables:

Person

 - Id
 - Name

PersonAttribute

 - Id
 - PersonId
 - Name
 - Value

Further, let's say that each person had 2 attributes (say, gender and age). A sample record would be like this:

Person->Id = 1
Person->Name = 'John Doe'

PersonAttribute->Id = 1
PersonAttribute->PersonId = 1
PersonAttribute->Name = 'Gender'
PersonAttribute->Value = 'Male'

PersonAttribute->Id = 2
PersonAttribute->PersonId = 1
PersonAttribute->Name = 'Age'
PersonAttribute->Value = '30'

Question: how do I query this such that I get a result like this:

'John Doe', 'Male', '30'

Answer 1

You need to JOIN the two tables. Wikipedia provides a pretty good explanation of JOIN: http://en.wikipedia.org/wiki/Join_%28SQL%29

Answer 2

Leaving the design aside, you can always PIVOT the result but you need to know how many attributes you are selecting out in advance.

Answer 3

I think you need redesign your schema. Why not?

Person

 - Id
 - Name
 - Gender
 - Birthday
...

Answer 4

SELECT p.name, p1.Value, p2.Value 
     FROM Person p, PersonAttribute p1, PersonAttribute p2 
     WHERE p.Id = p1.PersonId AND p.Id = p2.PersonId 
        AND p1.Name = 'Gender' AND p2.Name = 'Age'

Answer 5

SELECT Name, g.Value, a.Value
FROM Person, 
PersonAttribute g INNER JOIN ON g.Name = "Gender", 
PersonAttribute a INNER JOIN ON a.Name = "Age"

Answer 6

SELECT p.Name, g.Value, a.Value
FROM Person p INNER JOIN PersonAttribute g ON p.Id = g.Id AND g.Name = "Gender"
INNER JOIN PersonAttribute a ON p.Id = a.Id AND a.Name = "Age"

Answer 7

Storing Name Value pairs does give flexibility but is very cumbersome to query. Take a look at http://www.simple-talk.com/community/blogs/philfactor/archive/2008/05/29/56525.aspx

Answer 8

There's no easy way to do this.

The concept of a pivot table (already mentioned by another answer) is basically what you are looking for, except that pivot tables require you to know the names of the columns you wish to use. Clearly this is a problem when you want to exploit the power of such a table design!

In my previous life, I just settled on X number of columns, like 20-30, and if they didn't exist, then the row set included a bunch of null values. No big deal.

select piv.name, 
    max(case piv.a_name when 'Gender' then piv.a_value else null end) as Gender,
    max(case piv.a_name when 'Age' then piv.a_value else null end) as Age,
    max(case piv.a_name when 'Hobby' then piv.a_value else null end) as Hobby
from 
(select p.name as name, pa.name as a_name, pa.value as a_value 
from person p, personattribute pa
where p.id = pa.personid) piv
group by piv.name

This would generate output like so:

   name    | gender | age |  hobby  
-----------+--------+-----+---------
 Bob Swift | Male   |     | Reading
 John Doe  | Male   | 30  | 
(2 rows)

Which is pretty damned close to what you are looking for. I would leave the rest of it up to your application-layer.

I also highly recommend that you include the attribute NAME as part of the return value, to provide context to the VALUEs.

These types of so-called Entity-Attribute designs often end up having to rely on a combination of server-specific functions, stored procedures, and hard-coded queries.