Are There More Elegant Ways Of Querying Data Based On Values Stored In Attribute-value Table?

I have an attribute-value table av that looks like this: | attribute | value | ~~~~~~~~~~~~~~~~~~~~~ | a1 | A1 | | b1 | BB1 | | b2 | BB2 | For simplici

Solution 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(casewhen av.attribute ='a1'then av.value end) as a1,
               MAX(casewhen av.attribute ='b1'then av.value end) as b1,
               MAX(casewhen av.attribute ='b2'then av.value end) as b2
        from av
        groupby 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 crossjoin
       (selectMAX(casewhen av.attribute ='a1'then av.value end) as a1,
               MAX(casewhen av.attribute ='b1'then av.value end) as b1,
               MAX(casewhen av.attribute ='b2'then av.value end) as b2
        from av
       ) attr
       on attr.id = t1.id
where<whatever you want>

Solution 2:

Using the Entity-Attribute-Value design is fundamentally non-relational, so it's bound to be awkward and inefficient to query it in SQL as if the rows describe attributes of one logical entity.

To ease the cost of doing this in SQL, I frequently recommend to fetch all the rows as they are stored in the database, and then apply attributes to entity instances in application code, one row at a time.

Here's another SO question with example PHP code demonstrating what I mean: Create a summary result with one query

---

Re your comment and downvote:

You're shooting the messenger here.

You asked:

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?

Every method of querying multiple rows of EAV data into one row of result set, whether by joins or by pivoting, requires that you know the set of attributes, and these are fixed at the time you prepare the query. Because SQL is based on the relational model, the columns of a result-set cannot expand dynamically as the query executes.

Therefore, if you have a data model where the number of tables and attributes expands from time to time, you'll find yourself changing your pivot-query code every time you add an attribute.

You can generate the pivot-query dynamically based on the number of distinct attributes, but that also requires application code, because you need to query the current attributes and then build a query.

The alternative is to fetch all the data, one attribute per row of result set, and write code to "reassemble" them into logical entities on the application side.

Dynamic pivot queries always require application code -- either before or after the query.

I'm sorry you don't like that answer.