mysql select parent id of the records that match all values from array

Question

Hi there,

I have a problem with the sql query.

I have a joint table which stores records with reference id of the record in the parent table and id of the record from the other table.

Now - to make it a bit clearer:

Table 1: "products_properties" : 'id', 'product', 'price'

Table 2: "products_properties_options" : 'product_property', 'property_value'

Table 3: "properties_values" : 'id', 'property', 'value'

The table with the problem is the Table 2.

This table can store records which refer to one id (product_property) from the Table 1 and many ids (property_value) from Table 3.

Say I have the following records in this table:

product_property -> 1 / property_value -> 1

product_property -> 1 / property_value -> 2

product_property -> 1 / property_value -> 3

product_property -> 2 / property_value -> 1

product_property -> 2 / property_value -> 2

Now - I only want to get the 'product_property' value for records which 'property_value' matches the content of the array - say for instance array would contain: array(1, 2, 3) and I would also provide the WHERE product_property IN (1, 2) - this should return product_property => 1 as it matches all values from the array and its product_property is in 1 and 2.

and if I pass array(1, 2) and WHERE product_property IN (1, 2) this should return product_property => 2

Just to make it clear - this table won't store records with the same product_properties and property_value - I mean - there won't be a set with the same product_property and options.

Any idea how to achieve it?