Where clause on vertical Name/Value pair tables

  • Anyone has experience querying the data from "vertical table" having name value pairs?

    This is a sample table:

    --------------------------------------------------------

    ProductID, AttributeID, AttributeValue

    --------------------------------------------------------

    1,5,'golden',

    4,15,1

    2,6,'c'

    1,6,'e'

    --------------------------------------------------------

    this is a query aiming to filter ProductIDs only having all 3 attributes matching a criteria.

    SELECT

    ProductID

    FROM tblProductAttributeValues

    WHERE av.Active = 1 AND

    (AttributeID = 5 AND AttributeValue like  '%gold%') AND

    (AttributeID = 15 AND AttributeValue =1) AND

    (AttributeID = 6 AND AttributeValue ='C')

    This query would return no results because it does not find row containing AttributeID 5 and 6 in a single row.

    Anyone has experience with problems of similar nature? Shall I create a horizontal table to implement multi-column search ? Are there any other solutions? Is it a failure practice at all to use vertical tables in the environments where the number of attributes (columns) is unpredicatble ?

    I would highlly appreciate any ideas ...

    Thanks

    Simonas Miezanskas

  • That's an interesting problem.  This may not be the best way to do this...but, a quick way I would do it would be to:

    Create a temp table and change the query to:

    INSERT INTO TEMP_TABLE

    SELECT

    ProductID

    FROM tblProductAttributeValues

    WHERE av.Active = 1 AND

    (AttributeID = 5 AND AttributeValue like  '%gold%') OR

    (AttributeID = 15 AND AttributeValue =1) OR

    (AttributeID = 6 AND AttributeValue ='C')

    Then I would query the temp table with:

    SELECT ProductID, SUM(1) as recordcount

    FROM TEMP_TABLE

    GROUP BY ProductID

    HAVING SUM(1) = 3

    This way you will only get product ID's that had all three matches you were looking for (providing there will only be one record per attributeID.

    Ryan

  • Ryan beat me to it, however I would write the query as one, thus

    SELECT ProductID

    FROM tblProductAttributeValues

    WHERE av.Active = 1 AND

    ((AttributeID = 5 AND AttributeValue like  '%gold%') OR

     (AttributeID = 15 AND AttributeValue =1) OR

     (AttributeID = 6 AND AttributeValue ='C'))

    GROUP BY ProductID

    HAVING COUNT(*) = 3

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Thanks David and Ryan!

    I was supprised how quick and clear the answer was! It's very helpful!

    I have one more general question regarding the "Ideology" of vertical vs horizontal tables. What are the problems which I can run into choosing such a database design?

    Are there design paractices or other helpful materials about this kind of database schemas?

    I've been "googling" for a few days on this topic, but I don't see too much noice in the market. Therefore I'm not sure wherether I'm moving in a right direction at all?

    Thanks again!

    Simon

  • Simon, you may get a lot of differring opinions on this - and in many cases it will depend on the specific situation.  However, as a GENERAL guideline, I always try to use verticle.  For instance, your vertical table could have a composite PK of productID and AttributeID.  If this was a horizontal table you would choose just one of these values as the PK.  Then if you have one ProductID or AttributeID that requires 200 supporting columns (values for the attribute or product), every row of the table, whether needed or not, would require the same number of columns.  Just amagine the size.

    Also, imagine queries.  What exactly would you query?  What productID's or what AttributeID's would be in what columns.  If you had 200 columns, you could be looking at a where clause with 200 "OR" clauses.

    Again, this is an exagerated point - and deviations can be appropriate.  However, I go vertical more than 99% of the time.

    Ryan

  • How about :

    SELECT A1.ProductID

    FROM tblProductAttributeValues A1

    inner join tblProductAttributeValues A2

    on  A1.ProductID = A2.ProductID

     and A2.Active = 1 

     AND A2.AttributeID = 15

    AND A2.AttributeValue =1

    inner join tblProductAttributeValues A3

    on  A1.ProductID = A2.ProductID

     and A3.Active = 1

     AND A3.AttributeID = 6

    AND A3.AttributeValue ='C'

    WHERE A1.Active = 1

          AND A1.AttributeID = 5

         AND A1.AttributeValue like  '%gold%'

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply