asked on

SQL Query - getting unique items only

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;
--GO
--SELECT *
--FROM ORDER_ITEM
--WHERE Flavor <> 'Orange';
--GO
--SELECT *
--FROM ORDER_ITEM
--WHERE Flavor <> 'Orange'
--AND Flavor <> 'Licorice';
--GO
--SELECT *
--FROM ORDERS_IN
--WHERE Order_No NOT IN (SELECT Order_NO
-- FROM ORDER_ITEM
-- WHERE Flavor <> 'Orange'
-- AND Flavor <> 'Licorice');
SAMPLE.doc

Eyal

try something like this...

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')) AND NOT EXISTS(SELECT 1 FROM ORDERITEMS WITH(NOLOCK) oi1 WHERE oi1.orderid = o.orderid AND ((i.description <> N'gadget') OR
               (i.description <> N'gizmo')))
ORDER BY oi.OrderId ASC;

Open in new window

ocdc

ASKER

It gives me too many orderids should give me only one. It should eliminate the orderids that has attachments to other description items.
This what I got so far and still gives me 13 rows. should give only one.

SELECT oi.OrderId AS 'OrderId'
FROM ORDERITEMS AS oi

WHERE EXISTS (SELECT i.PartId FROM INVENTORY AS i
WHERE oi.partid = i.PartId AND i.description LIKE '%gizmo%')
OR EXISTS(SELECT i.PartId FROM INVENTORY AS i
WHERE oi.PartId = i.PartId AND i.description LIKE '%gadget%')

and NOT EXISTS ( SELECT i.PartId FROM INVENTORY as i
WHERE oi.PartId = i.PartId AND i.description NOT LIKE '%gizmo%'
or i.description NOT LIKE '%gadget%')

ORDER BY oi.OrderId

appari

try this

select oi.orderid as 'order id'
from orderitems as oi
where exists(Select 1 from inventory as inv
where oi.partid = inv.partid and (inv.description like '%gizmo%' or inv.description like '%gadget%') )

and not exists ( Select 1 from inventory as inv
where oi.partid = inv.partid and inv.description not like '%gizmo%'
and inv.description not like '%gadget%')

ASKER CERTIFIED SOLUTION

ocdc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Eyal

I had mistake in the first post

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 LIKE N'gadget%') OR (i.description LIKE N'gizmo%')) 
AND NOT EXISTS(SELECT 1 FROM ORDERITEMS WITH(NOLOCK) oi1 INNER JOIN INVENTORY i1 ON oi1.partid = i1.partid WHERE oi1.orderid = o.orderid AND ((i1.description NOT LIKE N'gadget%') AND (i1.description N OT LIKE N'gizmo%')))
ORDER BY oi.OrderId ASC;

Open in new window

Eyal

pay attention that your solution has performance cost. but if you don't mind go for it.

Anthony Perkins

I suspect you may find the following a tad more performant:

SELECT  OrderId
FROM    (SELECT oi.OrderId,
                i.description
         FROM   ORDERITEMS oi
                INNER JOIN INVENTORY i ON oi.partid = i.partid
         WHERE  i.description IN (N'gadget', N'gizmo')
         GROUP BY oi.OrderId,
                i.DESCRIPTION
        ) o
WHERE   NOT EXISTS ( SELECT 1
                     FROM   ORDERITEMS oi
                            INNER JOIN INVENTORY i ON oi.partid = i.partid
                     WHERE  i.description NOT IN (N'gadget', N'gizmo') )
GROUP BY OrderId
HAVING  COUNT(*) = 2

Open in new window

ocdc

ASKER

just for the sake of the discussion your query below gives an error:

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 LIKE N'gadget%') OR (i.description LIKE N'gizmo%'))
AND NOT EXISTS(SELECT 1 FROM ORDERITEMS WITH(NOLOCK) oi1 INNER JOIN INVENTORY i1 ON oi1.partid = i1.partid WHERE oi1.orderid = o.orderid AND ((i1.description NOT LIKE N'gadget%') AND (i1.description N OT LIKE N'gizmo%')))
ORDER BY oi.OrderId ASC;

AND your other query below gives no data;

SELECT OrderId
FROM (SELECT oi.OrderId,
i.description
FROM ORDERITEMS oi
INNER JOIN INVENTORY i ON oi.partid = i.partid
WHERE i.description IN (N'gadget', N'gizmo')
GROUP BY oi.OrderId,
i.DESCRIPTION
) o
WHERE NOT EXISTS ( SELECT 1
FROM ORDERITEMS oi
INNER JOIN INVENTORY i ON oi.partid = i.partid
WHERE i.description NOT IN (N'gadget', N'gizmo') )
GROUP BY OrderId
HAVING COUNT(*) = 2

Anthony Perkins

>>AND your other query below gives no data;<<
Fair enough. Let me know if you want a corrected version.

Good luck.

ocdc

ASKER

I provided the answer.