Forum Moderators: open
For the sake of simplicity, lets say I have three tables:
-------
Product
-------
id
name
price--------------
ProductOption
--------------
id
name
------------------
ProductOptionValue
-------------------
id
productOptionId
productId
value
The productOptionValue table references both the ProductOption table (via productOptionId) and the Product table (via productId).
Now, when I render this to a page, I do something like this:
(note: this is pseudo HTML mark-up!)
<!-- Product Details -->
Options:
<select name="color"> <select name="size"> [Go]
Where the two selects allow users to select their prefernces before hitting Go.
What I need to then do is select all productId's that match these two parameters (color and size). The color and size are stored in the "name" field of the productOption table and the values are in the productOptionValue table. The reason I have done it this way (custom fields) is so that they can be arbitrary (e.g color/size/weight/finish/whatever).
I may also have an arbitrary amount of "custom" fields. In this example, I'm just using two (color and size).
The problem is, when I do a query like this:
SELECT productId FROM ProductOptionValue WHERE value = "Blue" AND value = "Large"
its obviously not going to work. I could join 1 or more of the "productOptionValue" tables together - but I think there is a better way out there and I'm just too tired to realise!
I appreciate your help!
On MySQL it would look something like this:
SELECT Product.id,
Product.name,
Product.price
FROM Product INNER JOIN (
SELECT ProductOptionValue.productId,
MAX(IF(ProductOption.name = "size", ProductOptionValue.value, NULL)) AS Product_Size,
MAX(IF(ProductOption.name = "color", ProductOptionValue.value, NULL)) AS Product_Color
FROM ProductOption INNER JOIN ProductOptionValue ON ProductOption.id = ProductOptionValue.productOptionId
GROUP BY ProductOptionValue.productId
)tmp ON Product.id = tmp.productId
WHERE tmp.Product_Size = "large"
AND tmp.Product_Color = "blue"
SELECT *
FROM ProductOptionValue AS pov, ProductOption AS po, Product AS p
WHERE pov.productOptionId = po.id
AND (pov.value = "Blue"
OR pov.value = "Large")
AND p.productId = p.id
But I don't think your db scheme is sufficient - how does it link together an item being red with an item being large?
By using productId
Thanks for your help guys. I'm using mySQL 4.1 so can't do nested selects. I managed to sort it out in the end.
