I have products on an ecommerce website that have options eg size and colour. If I display a product filter (form) with checkboxes for all available attributes for each available option and the selected attributes are posted to some code that selects the product records then the sql below will fetch any product that has an options_values_id of 1 (eg blue) or 2 (eg red) where the options_values might be colours blue and red
select p.products_id, pd.products_name from products p left join products_attributes pa on p.products_id = pa.products_id where pa.options_values_id in(1,2);
The following code would do the same:
select p.products_id, pd.products_name from products p left join products_attributes pa on p.products_id = pa.products_id where (pa.options_id = 1 and pa.options_values_id = 1) or (pa.options_id = 1 and pa.options_values_id = 2);
But how could I search for products with colour red or blue and
size medium where size is pa.options_id = 2 and medium is options_values_id = 3, thereby reducing results rather than increasing, without looping through all the
records and using an 'if' clause, can a union do this or a sub select?
It's difficult to present the full code and web page so if you need that then please don't comment.