Solved

SQL Query - getting  unique items only

Posted on 2013-01-20
10
316 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now