Take the 2-minute tour ×

I have table schem as'

Attribute_name attribute_value  Attr_id uniquey_keyId
  tag           A               111        1
  price         113             111        2
  product       B               111        3
  value         115             111        4

I need to select attribute_value when attribute_name = tag for all where attribute_value = 115 for each Attr_id;

Output should be" A

This is the case where we have multiple rows for the same id and spread across for different values.

share|improve this question
What have you tried? –  Kundan Singh Chouhan Aug 16 '12 at 17:47
Hi, and welcome to Stack Overflow. With questions like this it's often a lot better if you also post the result-set you want. This means that people have something to work to. It's polite if you post the methods you attempted when trying to solve your problem. This provides an indication of your thinking and helps others to hep. It also proves that you have attempted something for yourself and are not just asking for other people to do your work for you. –  Ben Aug 16 '12 at 17:50
add comment

3 Answers

This sort of entity-attribute data model is going to create major performance problems. You're going to constantly have to write self-joins to pivot the data into something that you can actually query. That is going to be slow and is going to scale very poorly.

That being said

SELECT t.attribute_value
  FROM (SELECT attr_id, attribute_name, attribute_value
          FROM table_name
         WHERE attribute_name = 'value') v,
       (SELECT attr_id, attribute_name, attribute_value
          FROM table_name
         WHERE attribute_name = 'tag') t
 WHERE t.attr_id = v.attr_id
   AND v.attribute_value = '115';

If you need to look at two attributes, you'd need to join the table to itself once as I did here. If you need to look at three attributes, you'd need two joins. If you need more attributes, you'd need more joins. This is unlikely to scale efficiently.

share|improve this answer
add comment

I think you can do this with a group by and fun having clause:

select attr_id, max(case when Attribute_name= 'tag' then attribute_value end)
from t
group by attr_id
having sum(case when Attribute_name= 'value'  and Attibute_value = '115'
                then 1 else 0
           end) > 0

This assumes that there is only one attribute called "tag" for each attr_id. If there are more, you need a slightly more complicated query. Can you assume there is at most one tag?

share|improve this answer
add comment

This is a typical application for a PIVOT query

SELECT * from (
    SELECT Attr_id, attribute_value, Attribute_name 
    FROM schem
    FOR Attribute_name IN ('tag','price','product','value')

This creates a table like

Attr_id  tag  price  product  value  
111       A    113     B       115  
112       X    90      C       50

I would create a view from this pivot query. Based on this view, your problem becomes very easy then

FROM pivot_view
WHERE value = '115'
share|improve this answer
add comment

Your Answer


