--products table
DECLARE @tblProducts TABLE (PID SMALLINT,
Name VARCHAR(10),
Price MONEY)
INSERT INTO @tblProducts(PID, Name, Price)
VALUES (1, 't1', 1), (2, 't2', 11), (3, 't3', 22), (4, 't4', 33), (5, 't5', 44)
--shipments table
DECLARE @tblShipping TABLE(ShipID SMALLINT,
TypeID SMALLINT,
PID SMALLINT,
Price MONEY)
INSERT INTO @tblShipping(ShipID, TypeID, PID, Price)
VALUES (1, 1, 1, 12), (2, 2, 1, 14), (3, 4, 1, 15), (4, 1, 2, 11), (5, 3, 2, 11), (6, 4, 2, 11),
(7, 1, 3, 11), (8, 2, 3, 11), (9, 3, 3, 11), (10, 1, 4, 12), (11, 5, 5, 13)
--table grouping products that should have common shipment types
DECLARE @prodgroups TABLE(PID SMALLINT, groupID CHAR(1))
INSERT INTO @prodgroups(PID, groupID)
VALUES (1, 'a'), (2, 'a'), (4, 'a'), --get common shipment types for the combination of products: 1, 2, 4
(2, 'b'),(3, 'b'), --get common shipment types for the combination of products: 2, 3
(1, 'c'), (5, 'c') --get common shipment types for the combination of products: 1, 5
SELECT groupprods.groupID, groupshipments.TypeID
FROM
(--get counter of services per group
SELECT groupID, COUNT(DISTINCT PID) AS groupprodcounter
FROM @prodgroups
GROUP BY groupID
)AS groupprods
LEFT JOIN
(--get counter of shipped products per group and shipment type
SELECT pgrp.groupID, shp.TypeID, COUNT(DISTINCT shp.PID) AS shipmentprodcounter
FROM @prodgroups AS pgrp
JOIN @tblShipping AS shp ON pgrp.PID = shp.PID
GROUP BY pgrp.groupID, shp.TypeID
) AS groupshipments ON groupprods.groupID = groupshipments.groupID AND groupprods.groupprodcounter = groupshipments.shipmentprodcounter
ORDER BY groupprods.groupID
--HTH--