CODEWITHSUNDEEP
sqlmysqldatabase
StackOverflowNewbie
StackOverflowNewbie

Reputation: 40653

Need help with SQL Query

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'

Upvotes: 3

Views: 178

Answers (8)

Timothy
Timothy

Reputation: 2477

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.

Upvotes: 1

Don
Don

Reputation: 9661

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

Upvotes: 1

Raju
Raju

Reputation: 415

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

Upvotes: 2

Chinjoo
Chinjoo

Reputation: 2832

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"

Upvotes: 2

mirza
mirza

Reputation: 960

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

Upvotes: 0

Amarghosh
Amarghosh

Reputation: 59471

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'

Upvotes: 4

msi77
msi77

Reputation: 1632

I think you need redesign your schema. Why not?

Person

 - Id
 - Name
 - Gender
 - Birthday
...

Upvotes: 3

EMP
EMP

Reputation: 62031

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

Upvotes: 0

Related Questions