If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL to get all products ordered together
I need help with T-SQL syntax. I need all products ordered together.
This would give me all products in an order. I don't want the orders with only one product. SELECT o.orderID, o.orderNum, ol.productName FROM order as o ON orderline as ol o.orderID = ol.orderID How do I get the SQL with 2 or more products in the order? thanks, |
#2
|
|||
|
|||
SQL to get all products ordered together
One method that should work.
SELECT o.orderID, o.orderNum, ol.productName FROM order as o INNER JOIN OrderLine as OL ON o.orderID = ol.orderID WHERE O.OrderID IN (SELECT Tmp.OrderID FROM Order as Tmp INNER JOIN OrderLine as OLA ON Tmp.OrderID = OLA.OrderID GROUP BY Tmp.OrderID HAVING Count(*) 1) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === JRough wrote: I need help with T-SQL syntax. I need all products ordered together. This would give me all products in an order. I don't want the orders with only one product. SELECT o.orderID, o.orderNum, ol.productName FROM order as o ON orderline as ol o.orderID = ol.orderID How do I get the SQL with 2 or more products in the order? thanks, |
#3
|
|||
|
|||
SQL to get all products ordered together
So I'm assuming you have to add a tmp table but I don't know why you
created the second alias OLA. Do you have to create a second alias when you use a separate select statement? Why can't you use the OL alias? Is this a correlated sub-query. I have to study this for awhile. it might take tonight. On Mar 20, 4:37 pm, John Spencer wrote: One method that should work. SELECT o.orderID, o.orderNum, ol.productName FROM order as o INNER JOIN OrderLine as OL ON o.orderID = ol.orderID WHERE O.OrderID IN (SELECT Tmp.OrderID FROM Order as Tmp INNER JOIN OrderLine as OLA ON Tmp.OrderID = OLA.OrderID GROUP BY Tmp.OrderID HAVING Count(*) 1) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === JRough wrote: I need help with T-SQL syntax. I need all products ordered together. This would give me all products in an order. I don't want the orders with only one product. SELECT o.orderID, o.orderNum, ol.productName FROM order as o ON orderline as ol o.orderID = ol.orderID How do I get the SQL with 2 or more products in the order? thanks, |
#4
|
|||
|
|||
SQL to get all products ordered together
It looks like you just created 2 new aliases for the sub query.
Thanks very much. I think I can figure it out. The answer is the having clause. On Mar 20, 4:37 pm, John Spencer wrote: One method that should work. SELECT o.orderID, o.orderNum, ol.productName FROM order as o INNER JOIN OrderLine as OL ON o.orderID = ol.orderID WHERE O.OrderID IN (SELECT Tmp.OrderID FROM Order as Tmp INNER JOIN OrderLine as OLA ON Tmp.OrderID = OLA.OrderID GROUP BY Tmp.OrderID HAVING Count(*) 1) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === JRough wrote: I need help with T-SQL syntax. I need all products ordered together. This would give me all products in an order. I don't want the orders with only one product. SELECT o.orderID, o.orderNum, ol.productName FROM order as o ON orderline as ol o.orderID = ol.orderID How do I get the SQL with 2 or more products in the order? thanks, |
#5
|
|||
|
|||
SQL to get all products ordered together
Acutally, I could have made that a bit simpler
SELECT o.orderID, o.orderNum, ol.productName FROM order as o INNER JOIN OrderLine as OL ON o.orderID = ol.orderID WHERE O.OrderID IN (SELECT Tmp.OrderID FROM OrderLine as Tmp GROUP BY Tmp.OrderID HAVING Count(*) 1) -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "JRough" wrote in message ... So I'm assuming you have to add a tmp table but I don't know why you created the second alias OLA. Do you have to create a second alias when you use a separate select statement? Why can't you use the OL alias? Is this a correlated sub-query. I have to study this for awhile. it might take tonight. On Mar 20, 4:37 pm, John Spencer wrote: One method that should work. SELECT o.orderID, o.orderNum, ol.productName FROM order as o INNER JOIN OrderLine as OL ON o.orderID = ol.orderID WHERE O.OrderID IN (SELECT Tmp.OrderID FROM Order as Tmp INNER JOIN OrderLine as OLA ON Tmp.OrderID = OLA.OrderID GROUP BY Tmp.OrderID HAVING Count(*) 1) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === JRough wrote: I need help with T-SQL syntax. I need all products ordered together. This would give me all products in an order. I don't want the orders with only one product. SELECT o.orderID, o.orderNum, ol.productName FROM order as o ON orderline as ol o.orderID = ol.orderID How do I get the SQL with 2 or more products in the order? thanks, |
Thread Tools | |
Display Modes | |
|
|