I'm a newbie in sql, please can someone help me out in constructing a query. What I want to achieve is to have filter/search menu with a list of the different options/optionsvalues that applies to a particular category of products and a count of the products that possess each attribute.
Say under COLOR we have Black (2), Blue(17), Green(21) and under SIZE we have 10m(11), 20m(15). My tables areas follows:
categories => catid, parentid
products => productid, catid
options => optionsid, options_name (e.g Colour, Size)
optionsvalues => optionsvaluesid, optionsvalues_name (e.g Black, Blue, Green, 10m, 20m, 30m)
options2optionsvalues =>options2optionsvaluesid, optionsid, optionsvaluesid
products_attributes => attributeid, productid, catid, optionsid, optionsvaluesid
I really need your help.