I need to modify the following query
SELECT DISTINCT oi.OrderId
FROM ORDERITEMS oi INNER JOIN
ORDERS o ON oi.orderid = o.orderid INNER JOIN
INVENTORY i ON oi.partid = i.partid
WHERE (i.description = N'gadget') OR
(i.description = N'gizmo')
ORDER BY oi.OrderId ASC;
basically to get:
An order (orderid) is to contain gadgets and gizmos and nothing else.
At a minimum, then, an order would have one gadget and one gizmo, period.
But an order could contain any number of different kinds of gadgets AND any number of different kinds of gizmos as long as it had NO OTHER PARTS.
Meaning, an orderid can have gadget or gizmo and the same orderid can have many other descriptions. I need only the orderids that has only gadget and gismo and those orderids not tied to any other description. Example. if orderid 100 has gadget or gizmo in inventory.description then orderid cannot be tied to any other inventory.description at the same time. Please see the attached sample file. Only unique orderids with gadget and gizmo. That is what I also need to exclude from the output of the query.
This sample format might help: This what I am looking turn my query above into so that I can get the correct result.
--SELECT * FROM ORDER_ITEM;
--WHERE Flavor <> 'Orange';
--WHERE Flavor <> 'Orange'
--AND Flavor <> 'Licorice';
--WHERE Order_No NOT IN (SELECT Order_NO
-- FROM ORDER_ITEM
-- WHERE Flavor <> 'Orange'
-- AND Flavor <> 'Licorice');