Sql Server 2008 Select From table with AND style conditions in related tables

Question

Given a model like this

ProductFacets contains the following data:

ProductId, FacetTypeId
1, 1
1, 2
2, 1
2, 3
3, 4
3, 5
4, 1
4, 2

I'd like to be able to select all Products which have a FacetTypeId of 1 AND 2. The result set should contain ProductIds 1 and 4

Answer 1

SELECT * FROM Product PROD WHERE PROD.ProductId IN(

SELECT P.ProductId pId FROM ProductFacets AS P 
WHERE P.FacetTypeId = 1 

AND EXISTS  
(
  SELECT *
  FROM ProductFacets AS P1 
  WHERE P1.FacetTypeid = 2 
        AND P1.ProductId = pId
)

AND NOT EXISTS  
(
  SELECT *
  FROM ProductFacets AS P2 
  WHERE P2.FacetTypeid NOT IN (1,2) 
        AND P2.ProductId = pId
)

)

There must be a better way to solve this, but it's the only one i can come up with

Answer 2

This will return rows for products that have only facet types 1 and 2, and only those facets.

SELECT ProductId,
    COUNT(*) AS FacetCountByProduct,
    SUM(CASE WHEN FacetTypeId in (1, 2) THEN 1 ELSE 0 END) AS FacetCountSelectedFacets
FROM ProductFacets
GROUP BY ProductId
HAVING COUNT(*) = 2
    and SUM(CASE WHEN FacetTypeId in (1, 2) THEN 1 ELSE 0 END) = 2
;

Answer 3

Just thought of a way to do this:

select distinct ProductId from ProductFacets 
where ProductId in (select ProductId from ProductFacets where FacetTypeId = 1)
and ProductId in (select ProductId from ProductFacets where FacetTypeId = 2)