renkai — 2010-10-12T15:39:01-04:00 — #1
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?
r937 — 2010-10-12T17:15:03-04:00 — #2
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
renkai — 2010-10-12T16:13:16-04:00 — #3
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
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.
guido2004 — 2010-10-12T15:43:27-04:00 — #4
What you need is an INNER JOIN:
FROM orderdetails AS od
INNER JOIN products AS p
ON od.productID = p.ID
WHERE od.orderID = xxxx
AND p.categoryID = yyy
renkai — 2010-10-12T15:47:17-04:00 — #5
wow... I understand INNER JOIN now!! Thank you!