Better way to select products that are related to ALL of the categories? I am trying to select products that are related to ALL of the categories, that has been "handed" to me.

The products are in the Product table and the relation to the categories are in the ProductCategoryRelation table.

The below code works, but I am not sure it is the fastest way to do it.

The 1,2,3 numbers are the category IDs.

SELECT * 
FROM Product
WHERE (
    SELECT Count(*) 
    FROM ProductCategoryRelation 
    WHERE ProductCategoryRelation.CategoryId IN (1,2,3) 
        AND ProductCategoryRelation.ProductId = Product.ProductId
    ) = 3
This might work for you, should perform better.

WITH CTE_ProductCount AS
(
    SELECT PC.ProductID
        , COUNT(PC.CategoryID) AS ProductCount
    FROM ProductCategoryRelation PC
    WHERE PC.CategoryID IN (1,2,3)
    GROUP BY PC.ProductID
    HAVING COUNT(PC.CategoryID) = 3
)
SELECT *
FROM Product
    INNER JOIN CTE_ProductCount PC ON PC.ProductID = Product.ProductID;