Get Products supporting all delivery modes in SQL

Question

I have a table ProductDeliveryModes as:

ProductId    DeliveryId
P101         D1
P101         D2
P101         D3
P102         D1
P102         D2
P102         D3
P103         D1

I need to get products which support all delivery modes (D1, D2, D3). From looking at the table the products should be: P101 and P102.

The query that I formed to get the solution is:

SELECT ProductId
FROM   (SELECT DISTINCT ProductId,
                        DeliveryId
        FROM   ProductDeliveryModes) X
WHERE  X.DeliveryId IN ( 'D1', 'D2', 'D3' )
GROUP  BY ProductId
HAVING COUNT(*) = 3 

The problem that I see in my solution is that one should know the count of the total number of delivery modes. We could make the count dynamic by getting the count from Sub-query.

Is there a better solution ?

Answer 1

I believe you can use DISTINCT with COUNT function to get the same result:

SELECT [ProductID]
FROM ProductDeliveryModes
GROUP BY  [ProductID]
HAVING COUNT(DISTINCT [DeliveryId]) = 3

Check the example.

You can simple store the distinct delivery count in a variable and used it. If you need to do this in a single query, this is one of the possible ways:

WITH CTE (DeliveryCount) AS 
(
   SELECT COUNT(DISTINCT [DeliveryID])
   FROM DataSource
)  
SELECT [ProductID]
FROM DataSource
CROSS APPLY CTE
GROUP BY [ProductID]
        ,CTE.DeliveryCount
HAVING COUNT(DISTINCT [DeliveryID]) = DeliveryCount

See the example.

Answer 2

you can use this below query for better performance.

 ;WITH CTE_Product
 AS
 (
        SELECT DISTINCT ProductID 
        FROM ProductDeliveryModes
 ),CTE_Delivery
 AS
 (
        SELECT DISTINCT DeliveryId 
        FROM ProductDeliveryModes
 )

 SELECT * 
 FROM CTE_Product C
 WHERE NOT EXISTS
 (
     SELECT 1 
     FROM CTE_Delivery D
     LEFT JOIN ProductDeliveryModes T ON T.DeliveryId = D.DeliveryId AND T.ProductId=C.ProductId 
     WHERE T.ProductID IS NULL
 ) 

Answer 3

You can modify your query just a bit to get the actual count of distinct delivery methods:

SELECT ProductID
FROM ProductDeliveryModes
GROUP BY ProductID
HAVING COUNT(*) =
   (SELECT COUNT (DISTINCT DeliveryId) FROM ProductDeliveryModes)