asked
Which of the customers who ordered bracelets have not ordered necklaces?
[link text][1]![alt text][2] [1]: /storage/temp/4600-many-charms-info-2410.docx [2]: /storage/temp/4598-lucid-chart.png
@WRBI Judging by the crowfeet icons, this appears to be Oracle database (I might be wrong but it does look like Oracle to me). @nathan960 Could you please clarify your question because the way it is worded is ambiguous. What does "**the customers who ordered bracelets have not ordered necklaces**" mean? Does it mean you need to find the customers who ordered bracelets and have not included necklaces in ***the same order***? Or it means the customer who have a history of orders of bracelets ***never ordered*** necklaces. Please clarify.
This appears to be following-on / duplicate of the earlier question
https://ask.sqlservercentral.com/questions/146870/1-which-of-the-customers-who-ordered-bracelets-hav.html - which I have now closed, as this one actually provides useful information if anyone's inclined to help.
It would help if you included a create script and some sample data. I personally don't want to spend 30 minutes setting up tables, schemas and populating data to write a query. Also, is this SQL Server or Oracle? Stops us from using functions for SQL Server that don't exist in Oracle etc.
@nathan960 By just glancing over the utterly terrible design (IMHO) this appears to be a homework, and the attached document shows that there have been some effort toward solving the questions, so it is OK to help with this one. Usually, the users who post answers on this site are glad to help, but will not do someone's homework unless the effort has been shown. Here is just one reason why I think that the design is terrible: Item table includes the column named InventoryQty. This can never happen in real life. Whenever someone includes the item in the order (record gets inserted into SaleItem), the only way to keep InventoryQty accurate is by updating the Item table at the same time. What about the situation when the supplier fulfills the purchase order(brings in more items)? Same update needs to happen. What about returns, damage, expired items? InventoryQty does not belong in Item table, so the design is flawed. I will try to come up with the query for a specific question about bracelets later this evening, but honestly, if I were to grade this project I would have a hard time grading it any more than zero because every query so far is incorrect.
Following on from my above comment. Try the types of query that are the answers in the following post:
https://ask.sqlservercentral.com/questions/146858/comparing-rows-in-same-column-sql-server.html
Here are the queries which will produce desired results. There is no need to elaborate much on such a simiple problem, the queries are very straightforward, and include comments explaining the logic. Any of the 3 methods will answer the question correctly. In order to get familiar with CROSS APPLY, please read the 2-part series by Paul White: - [Understanding and Using APPLY (Part 1)][1] - [Understanding and Using APPLY (Part 2)][2] Here are the scripts: -- method 1 using the cross apply. This allows to reveal not only the required information but also -- to calculate how many bracelets were purchased by specific customer who never bought neclaces select c.CustID, c.CustFN + ' ' + c.CustLN CustFullName, b.BraceletCount, isnull(n.NeclaceCount, 0) NeclaceCount from dbo.Customer c cross apply ( select sum(si.SlQty) BraceletCount from dbo.Sale s inner join dbo.SaleItem si on s.SaleID = si.SaleID inner join dbo.Item i on si.ItemID = i.ItemID -- match by customer and count only those orders which included bracelets where s.CustID = c.CustID and i.Category = 'Bracelet' ) b cross apply ( select sum(si.SlQty) NeclaceCount from dbo.Sale s inner join dbo.SaleItem si on s.SaleID = si.SaleID inner join dbo.Item i on si.ItemID = i.ItemID -- match by customer and count only those orders which included neclaces where s.CustID = c.CustID and i.Category = 'Neclace' ) n -- if the customer has history of orders which included neclaces then b.BraceletCount -- will be NOT NULL. Similarly, n.NeclaceCount will be NOT NULL for neclace orders, so where b.BraceletCount is not null and n.NeclaceCount is null; -- method 2, using the correlated subquery in the WHERE clause. This method does not retrieve -- any details about the items quantity, it only answers the question, that is all. select c.CustID, c.CustFN + ' ' + c.CustLN CustFullName from dbo.Customer c where exists ( select 1 from dbo.Sale s inner join dbo.SaleItem si on s.SaleID = si.SaleID inner join dbo.Item i on si.ItemID = i.ItemID where s.CustID = c.CustID and i.Category = 'Bracelet' ) and not exists ( select 1 from dbo.Sale s inner join dbo.SaleItem si on s.SaleID = si.SaleID inner join dbo.Item i on si.ItemID = i.ItemID where s.CustID = c.CustID and i.Category = 'Neclace' ); -- method 3, using inner join to both bracelets and neclace sales history at once and -- then requiring that the count of neclaces is 0 -- Because it would be very cumbersome to join on the sales related tables directly -- because there could be multiple orders for the same customer, a join to the subquery is used -- instead. All 3 mehods should be tested and then the best by performance then should be used. select c.CustID, c.CustFN + ' ' + c.CustLN CustFullName, sales.BraceletCount, sales.BothCount - sales.BraceletCount NeclaceCount from dbo.Customer c inner join ( select s.CustID, sum(si.SlQty) BothCount, sum(case when i.Category = 'Bracelet' then si.SlQty else 0 end) BraceletCount from dbo.Sale s inner join dbo.SaleItem si on s.SaleID = si.SaleID inner join dbo.Item i on si.ItemID = i.ItemID where i.Category in ('Bracelet', 'Neclace') group by s.CustID ) sales on c.CustID = sales.CustID where BothCount = BraceletCount; /* customer bought bracelets only, no neclaces */ go Hope this helps. Oleg [1]:
http://www.sqlservercentral.com/articles/APPLY/69953/ [2]:
http://www.sqlservercentral.com/articles/APPLY/69954/
19 People are following this question.
