Multiple AND conditions on the same column [Servicestack.OrmLite]

Question

I was wondering if it's possible to have multiple AND conditions on the same column using Servicestack.OrmLite. This is the SELECT statement I printed out, but It always returns 0. I should get the product count from the products having both specifications with id 1016 and 17.

SELECT COUNT(DISTINCT "Product"."Id") 
FROM "Product" 
INNER JOIN "ProductManufacturer" 
ON ("Product"."Id" = "ProductManufacturer"."ProductId") 
INNER JOIN "ProductSpecificationAttribute" 
ON ("Product"."Id" = "ProductSpecificationAttribute"."ProductId")
WHERE ("ProductManufacturer"."ManufacturerId" = 6) 
AND ("ProductSpecificationAttribute"."SpecificationAttributeOptionId" = 1016) 
AND ("ProductSpecificationAttribute"."SpecificationAttributeOptionId" = 17)

Answer 1

You can get all the product ids that you want if you group by product and set the conditions in the HAVING clause:

SELECT p.Id
FROM Product p
INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId 
INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY p.Id
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2; -- both specifications must exist

If you want to count these products you could either use the above query as a subquery or a cte and count the rows:

WITH cte AS (
  SELECT p.Id
  FROM Product p
  INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId 
  INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
  WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
  GROUP BY p.Id
  HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2; 
)
SELECT COUNT(*) FROM cte;

or, use COUNT() window function:

SELECT DISTINCT COUNT(*) OVER ()
FROM Product p
INNER JOIN ProductManufacturer pm ON p.Id = pm.ProductId 
INNER JOIN ProductSpecificationAttribute psa ON p.Id = psa.ProductId
WHERE pm.ManufacturerId = 6 AND psa.SpecificationAttributeOptionId IN (17, 1016)
GROUP BY p.Id
HAVING COUNT(DISTINCT psa.SpecificationAttributeOptionId) = 2;