What is the SQL for this?

All I want to know is if an order contains any product that belongs to a specific category. categoryID is not on the orderdetails table, however.

So I start with my query on the order detail table. Something like select orderID, productID from orderdetails where orderID = xxxx. This will return multiple results of productIDs. I then want to take each productID and look up the products table to determine if each product belongs to a specific category. If I get any match or not is all I want to know.

What SQL statements might achieve this result? Can a single query statement get me the result?

yes

start with FROM Orders as you have there

then add INNER JOIN OrderDetails ON …

then add INNER JOIN Products ON …

then put the columns you want into the SELECT clause

well this is getting more complex again. Actually what I have to start with is a list of each order from the order table, but problem is the order table does not contain information about the product at all. Instead, the orderdetails table has that. So I have to incorporate the inner join statement into a select statement on the order table. Make sense?

What I’m starting with is this:

SELECT ID, OrderDate, ShipDate
FROM Orders
WHERE BillToID= xxx

Is there a way to incorporate the other query that looks up the orderdetails table? Each row I need a field that says yes or no - a product from that order either does or does not belong to a category.

What you need is an INNER JOIN:


SELECT
    od.orderID
  , od.productID 
FROM orderdetails AS od
INNER JOIN products AS p
ON od.productID = p.ID
WHERE od.orderID = xxxx
AND p.categoryID = yyy

wow… I understand INNER JOIN now!! Thank you! :smiley: