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,

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,

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,

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,

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,