SQL to get all products ordered together

[JRough]

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,

 

[John Spencer]

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]

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.

 

[JRough]

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.

 

[John Spencer]

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
..