TrooperEOF TrooperEOF - 3 months ago 8
SQL Question

SQL - Return group of records from different tables not including specific row

I am new at programming and SQL, so sorry if I do not include enough info.

[I have these 2 tables that are linked by an OrderID. Table1 includes OrderIDs and customer information such as FirstName, LastName, and Address. Table2 includes OrderIDs and order details such as ItemName, Price, and Quantity.

Each OrderID in Table2 might have multiple ItemName entries with the same OrderID.]1

CustInfo

OrderID FirstName LastName Address
1 Bob Pratt 123
2 Jane Doe 456
3 John Smith 789
4 Barbara Walters 147


Orders

OrderID ItemName Price Quantity
1 Milk 4.00 1
1 Eggs 5.00 2
2 Cheese 5.00 1
2 Bread 5.00 1
3 Milk 4.00 2
4 Yogurt 5.00 2


I'm trying to make a query that will send back a list of every Order, listing the OrderID and ItemName among other info, as long as the order doesn't include a specific type of item (which would be in ItemName). So if an OrderID contains 2 ItemName, one of which is the one I do not want, the entire order (OrderID) should not show up in my result.

For example, based off the img included, if I wanted to show all orders as long as they do not have Milk as an ItemName, the result should only show OrderID 2 and 4.

2 Cheese 5.00 1
2 Bread 5.00 1
4 Yogurt 5.00 2


This is what I have tried but this would return OrderIDs even though Milk is technically part of that OrderID.

SELECT OrderID, FirstName, LastName, ItemName, Price, Quantity
FROM CustInfo
JOIN Orders
ON CustInfo.OrderID = Orders.OrderID
WHERE ItemName != 'Milk'


Can you help?

Answer
select o.OrderID, o.ItemName, c.FirstName, c.LastName -- include other fields if needed
from Orders o
left join CustInfo c on o.OrderID = c.OrderID
where o.OrderID not in (
  select OrderID from Orders where ItemName = 'Milk'
)