[Solved] Complicated MySQL Query (Sets)

[eluser]nzmike[/eluser]
I'm having a bit of trouble coming up with a complicated MySQL query.

I have three tables:

product
* id

product_tags
*product_id
*tag_id

tag
*id

Let's say the user is searching for products that have tags 1 & 2.

How would I construct a query which returns the products which have both tags.

So far my I have tried the following ways:

Code:

SELECT * FROM product, product_tag WHERE product.id = product_tag.product_id  AND product_tag.id IN(1,2);

This kind of worked but it would return results which meet either tags, I need a query which will only return results that have both.

Code:

SELECT * FROM product, product_tag WHERE product.id = product_tag.product_id  AND product_tag.id = 1 AND product_tag.id = 2;

Seemed fine in my head but when I ran in it returned nothing. Once I actually thought about it it's obvious why, how can a product_tag.id be both 1 and 2.

So now I'm some what stuck. The only way I can see of doing this is using the first query and grouping on the product.id then doing a count on the resulting group to make sure it matches the number of tags I am searching on. I thought I'd post here because I might be missing something. Is there an easy way of matching a whole set in MySQL?