Are there more elegant ways of querying data based on values stored in attribute-value table?

Question

I have an attribute-value table av that looks like this:

| attribute | value |
~~~~~~~~~~~~~~~~~~~~~
| a1        | A1    |
| b1        | BB1   |
| b2        | BB2   |

For simplicity, assume varchar(255) on both attribute and value columns, unique index on attribute.

I need to use the values of specific attributes in a query, which looks like this:

SELECT *
FROM   t1
      ,t2
WHERE  t1.a1 = "A1"  -- Value of "a1" attribute
 AND   t1.id = t2.id
 AND   t2.b1 = "BB1"  -- Value of "b1" attribute
 AND   t2.b2 = "BB2"  -- Value of "b2" attribute

Is there an elegant way of doing this in Sybase ASE (12 or 15) which scales well as we increase the # of tables and attributes?

By "scale" I mean ~10-20 attributes needed across 4-5 joined tables

I can think of the following solutions, all of which seem to suck:

---

SOLUTION 1: Obvious: Join AV table, once per attribute

SELECT *
FROM   t1
      ,t2
      ,av AS 'av_a1'
      ,av AS 'av_b1'
      ,av AS 'av_b2'
WHERE  t1.a1 = av_a1.value
 AND   t1.id = t2.id
 AND   t2.b1 = av_b1.value
 AND   t2.b2 = av_b2.value
 AND   av_a1.attribute = "a1"
 AND   av_b1.attribute = "b1"
 AND   av_b2.attribute = "b2"

Pros: Obvious.

Cons: Scales VERY poorly as far as code quality, and probably performance as well.

---

SOLUTION 2: Avoid the headache of multiple joins with variables

declare @a1 varchar(255)
select  @a1 = value FROM av WHERE attribute = "a1"
declare @b1 varchar(255)
select  @b1 = value FROM av WHERE attribute = "b1"
declare @b2 varchar(255)
select  @b2 = value FROM av WHERE attribute = "b2"

SELECT *
FROM   t1
      ,t2
WHERE  t1.a1 = @a1
 AND   t1.id = t2.id
 AND   t2.b1 = @b1
 AND   t2.b2 = @b2

Pros: No more extra joins making the query both ugly and poorly performing.

Cons: Scales somewhat poorly as far as code quality (need to add new variables with new attributes).

---

Any better solutions?

Answer 1

I'm not sure what the additional clauses in the where statement are for (comparing values in one table to the attributes in the other). The following flattens the attributes before the join:

SELECT *
FROM   t1 join
       t2
       on t1.id = t2.id join
       (select av.id,
               MAX(case when av.attribute = 'a1' then av.value end) as a1,
               MAX(case when av.attribute = 'b1' then av.value end) as b1,
               MAX(case when av.attribute = 'b2' then av.value end) as b2
        from av
        group by av.id
       ) attr
       on attr.id = t1.id

This works, assuming there are no duplicates in the attributes -- which there generally are not when using an attribute table. You can add back in the where conditions, if you like, I just didn't understand why they were there.

Also, you should switch to ANSI standard join syntax.

If you don't have an id, you can do essentially the same thing:

SELECT *
FROM   t1 join
       t2
       on t1.id = t2.id cross join
       (select MAX(case when av.attribute = 'a1' then av.value end) as a1,
               MAX(case when av.attribute = 'b1' then av.value end) as b1,
               MAX(case when av.attribute = 'b2' then av.value end) as b2
        from av
       ) attr
       on attr.id = t1.id
where <whatever you want>