Query for matching multiple rows?

Question

We have next tables in our system

Table object

object_id    object_description
    1             "Car"
    2             "Person"

Table attribute

attribute_id    attribute_name
     1             "hair_color"
     2             "height"
     3             "number_of_doors"
     4             "engine_size"

Table attribute_value

attribute_id    attribute_value_id    value
    1                1                "black"
    1                2                "blonde"
    2                1                "more than 1 meter"
    2                2                "less than 1 meter"
    3                1                "5 doors"
    3                2                "3 doors"
    4                1                "more than 1.9"
    4                2                "less than 1.9"

Table object_attribute

object_id    attribute_id    attribute_value_id
    1            3                1 -- Car, number of doors,5
    1            3                2 -- Car, number of doors,2
    1            4                1 -- Car, engine size, greater than 1.9
    1            4                2 -- Car, engine size, less than 1.9

With this structure we are having a lot of problems getting objects that match multiple criterias (i.e. get all cars with 3 doors and engine size bigger than 1.9) Currently we are using INTERSECTS for doing this

SELECT OBJECT_ID
  FROM object_attribute
 WHERE  attribute_id       = 3
   AND attribute_value     = 2 
INTERSECT 
SELECT OBJECT_ID
  FROM object_attribute
 WHERE  attribute_id       = 4
   AND attribute_value     = 1

There are diferent objects with diferent number of attributes, so we can't use a fixed number of JOINs or INTERSECTs anymore

Is there any way of generate multiple combinations of all attributes in a "dynamic way"?

What we would like to achieve is a dynamic query that builds a view like this:

object_id | att_name_1 | att_value_1 | att_name_2 | att_value2 | att_name_n | attr_value_n

As the number of attributes is variable we should trigered and update of the query when a new object is inserted

Guys I think what I have in mind is not possible, so we will probably go with this dynamic query construction at runtime. Thank you all for your answers

Answer 1

after some tests i came up with the following query:

select distinct 
a.attribute_name, o.object_description, av.value,
oa.attribute_id, oa.object_id, oa.attribute_value_id
from object_attribute oa
inner join attribute a on (oa.attribute_id = a.attribute_id and a.attribute_id = 3)
inner join object o on (oa.object_id = o.object_id and o.object_id = 1)
inner join attribute_value av on (oa.attribute_value_id = av.attribute_value_id and av.attribute_value_id = 2)
where 
(av.attribute_id = 3 and o.object_id = 1 and av.attribute_value_id = 2)

union
select distinct 
a.attribute_name, o.object_description, av.value,
oa.attribute_id, oa.object_id, oa.attribute_value_id
from object_attribute oa
inner join attribute a on (oa.attribute_id = a.attribute_id and a.attribute_id = 4)
inner join object o on (oa.object_id = o.object_id and o.object_id = 1)
inner join attribute_value av on (oa.attribute_value_id = av.attribute_value_id and     av.attribute_value_id = 1)
where 
(av.attribute_id = 4 and o.object_id = 1 and av.attribute_value_id = 1)

which results in the following:

If you are using MS SQL Server I would put it in a stored procedure that accepts the three Id's as parameters.