Multiple Refines from One Table

[grayashe]

Hi,

 

I have a sql statement that fetches all products from a certain category that follows a bunch of rules including checking it has stock and all relevant information etc.

 

The user has the option to refine by a number of fields. One field is a price band (ie between $10 - $20). I have this section working fine and it is no problem. The other refine by's however are dynamic in the following manner.

 

The site admin can create a spec type. So they create a spec type of color and apply it to all products. Then in the product catalog they can specify a color for each product. The spec data is stored in a table that holds the product id, that spec id and the spec value.

 

example:

 

product_id    spec_id    spec_value

1                  1              red

2                  1              blue

3                  1              red

1                  2              nike

2                  2              nike

3                  2              addidas

 

So, my problem is that I can refine by one spec no problem with something like this:

 

WHERE spec_id = 1 AND spec_value = 'red'.

 

However, if I want to be able to refine by two spec types (say we have a second spec type of 'brand' with a spec_id of 2). I want to be able to say return anyting that has a color of 'red' and a brand of 'nike', I can't use the following:

 

WHERE spec_id = 1 AND spec_value = 'red' AND spec_id = 2 AND spec_value = 'nike' as it will clash and return nothing.

 

I can't use

 

WHERE (spec_id = 1 AND spec_value = 'red') OR (spec_id = 2 AND spec_value = 'nike') as it will return anything that matches either statement.

 

I thought about re-joining on the spec table multiple times, and using an additional join for each type of refine, but it caused my statement to slow to a crawl.

 

So basically, what I want to know, is how do I write my statement to say, let me know which products are both 'red' and 'nike'. Basing it on the example table structure provided, this criteria would return only one product (product id of 1)

 

If you need any more clarification, please let me know.

Thanks

[grayashe]

Anybody have any ideas?

[fenway]

I don't follow.