Hi Sean

The solution I was looking for is something like this :

SELECT

p.ProductID,

Dept1ProdCount = COUNT(CASE WHEN t.ProductSoldInDept1 = p.ProductID THEN 1 END),

Dept2ProdCount = COUNT(CASE WHEN t.ProductSoldInDept2 = p.ProductID THEN 1 END),

Dept3ProdCount = COUNT(CASE WHEN t.ProductSoldInDept3 = p.ProductID THEN 1 END)

FROM dbo.Product AS p

LEFT OUTER JOIN dbo.[Transaction] AS t

ON p.ProductID IN

(t.ProductSoldInDept1, t.ProductSoldinDept2, t.ProductSoldinDept3)

GROUP BY p.ProductID;

Thank you !