January 17, 2005 at 7:18 am
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
January 17, 2005 at 7:29 am
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
January 17, 2005 at 7:32 am
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.
January 17, 2005 at 7:57 am
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
January 17, 2005 at 8:07 am
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
January 17, 2005 at 8:09 am
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