?
Solved

SQL Query - getting  unique items only

Posted on 2013-01-20
10
Medium Priority
?
344 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

765 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