Finding products that match options

Dinky

Hello.

Im making a webshop where each product has X options connected to them.

It is easy enhough to find out which options are connected to each product. That is because the product_to_option table has a product_id field. That means it is easy to make a product page with the right options.

The hard part is the other way around. How do I query for finding out which combination of options are connected to what product?

Tabeller:

Products

ID Name
01 T-shirt

02 Cardigan

Options

ID Name
01 Green
02 Red
03 XLarge

04 Small

Product_to_option

ID Product_id option_id
01 01(t-shirt) 01(green)
02 01(t-shirt) 03(xlarge)
03 02(cardigan) 02(red)

04 02(cardigan) 04(small)

Obviously there should be more fields in the product group and a table called option group to, well group options. But I have tried to keep it as basic as possible.

Any help while be greatly appreciated.

Piranha

I think the following will work:

select product_id
from product_to_option
where option_id in (2, 4)
group by product_id
having count(product_id) = 2

The count should be the same value as the number of options. Note that in order to get this to work the combination of product_id and option_id should be unique.

Dinky

Good show old chap 🙂

That worked like a charm! You are a lifesaver!!!!

I will try it out some more and give some feedback

Thanks, take care

Dinky

I found out what was wrong.

products.id = 2

should be

products.master = 2

Hello again.

Got the problem you referred to earlier.

The count should be the same value as the number of options. Note that in order to get this to work the combination of product_id and option_id should be unique.

The table "Products" actually also have a "master" field that referers to all products that are "slaves":

ID Name master
01 T-shirt 0
02 Cardigan (slave) 3

03 Cardigan (master) 0

So theroetically I can narrow down the query by adding JOIN or something so the query you suggested would be unique.

I tried this with no luck:

SELECT products.id AS pid,options_to_products.product_id AS oppid
FROM products,options_to_products
WHERE products.id = options_to_products.product_id AND products.id = 2 AND options_to_products.option_id IN (2, 4)
GROUP BY options_to_products.product_id
HAVING count(options_to_products.product_id) = 2