SQL query on link table where results must contain certain value

SQL Server 2005.

I've a link table of Products and Attributes associated with them. I'm doing a search along the lines of:

select distinct ProductId  from productattributelink where
attributeid in (25,5,44,46)

But I want to make sure that each productid is also associated with an attributeId of 10.

So i开发者_运维知识库n longhand the query would be: Show me all the product Ids that have the following attributeids (25,5,44,46) but also have attributeid of 10.

I've a feeling this is really obvious but is eluding me.

---

select distinct p.ProductId  from product p
inner join productattributelink pa1 on pa1.ProductId = p.ProductId
inner join productattributelink pa2 on pa2.ProductId = p.ProductId
where pa1.attributeid IN(25,5,44,46) and pa2.attributeid = 10

---

You should join the table to itself:

select distinct ProductId  from productattributelink p1 
JOIN productattributelink p2 ON p1.ProductId = p2.ProductId 
where p1.attributeid in (25,5,44,46) AND p2.attributeid = 10