Solved

SQL Query - getting  unique items only

Posted on 2013-01-20
10
338 Views
Last Modified: 2013-01-25
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
0
Comment
Question by:ocdc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:Eyal
ID: 38799815
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

0
 

Author Comment

by:ocdc
ID: 38799860
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
0
 
LVL 39

Expert Comment

by:appari
ID: 38799899
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%')
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
ocdc earned 0 total points
ID: 38799951
No. Still gives too many records.  here is my solution:

SELECT OrderId AS 'Order ID'
FROM  ORDERITEMS  
WHERE   OrderId IN ( SELECT OrderId
                     FROM ORDERITEMS
                     WHERE PartId IN (SELECT PartId
                     FROM INVENTORY
                     WHERE description LIKE 'gadget%'))
                     
    AND OrderId IN ( SELECT OrderId
                     FROM ORDERITEMS
                     WHERE PartId IN (SELECT PartId
                                      FROM INVENTORY
                                      WHERE Description LIKE 'gizmo%'))

   AND OrderId NOT IN ( SELECT OrderId
                        FROM ORDERITEMS
                        WHERE PartId  IN (SELECT PartId
                                          FROM INVENTORY
                                          WHERE description NOT LIKE 'gadget'
                                          AND description NOT LIKE 'gizmo'))
GROUP BY OrderId
ORDER BY 'Order ID' ASC;
0
 
LVL 15

Expert Comment

by:Eyal
ID: 38799965
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

0
 
LVL 15

Expert Comment

by:Eyal
ID: 38799969
pay attention that your solution has performance cost. but if you don't mind go for it.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38803531
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

0
 

Author Comment

by:ocdc
ID: 38803987
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38805498
>>AND   your other query  below gives no data;<<
Fair enough.  Let me know if you want a corrected version.

Good luck.
0
 

Author Closing Comment

by:ocdc
ID: 38818043
I provided the answer.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question