Searching data in a many to many relationship

[Jak-S]

Hi
I need to work out how to search some data in a MySQL database but I’m completely stuck and don’t have a clue how to do it.

There are four tables. Variables contains a list of different variables that can be associated to products. Options is a child of variables and contains all the different options available for the variable (for example a variable called Colour may have Blue, Red and Green options). Products contains all the products available.

The last table is called product_options and links the products table to the options table in a many-to-many relationship. So, you may have two variables called Colour and Size, and then a product that is linked to Blue and Large.

My question is, how do I search this data to find say, all the products that are blue, or all the products that are blue and large etc.

All I’ve got so far is a query that links the four tables together using inner joins, however this is totally wrong as a product that’s linked to three options will appear three times, and grouping them doesn’t help because then you loose two options that you may wish to search on.

Thanks in advance, I’m REALLY stuck with this.

[aussie girl]

Well first of all you shouldn't have a many to many relationship unless it's the associative table..and not knowing exactly what tablenames and colum names you have try something like

SELECT columnname, columnane, columname, etc
FROM Variables, Options,Products, product_options
WHERE Variables.var_id = product_options.var_id
AND Options.option_id = product_options.option_id
AND Products.product_id = product_options.product_id
AND Colour = 'Blue' AND Size = 'Large';

Hope this helps