SQL subquery and aggregate function

Question

I am just learning about SQL subqueries, and I am a little confused as to why this isn't working. I have two tables, Order and OrderDetails. They both have an OrderID, and the OrderDetails has the fields OrderDetails.ProductID and OrderDetails.Quantity. So different orders can have different products, and on the OrderDetails table, there can be multiple rows for an OrderID if the Order has more than one different product.

so on the Orders Table it could be like

OrderID

10248

10249

10250

and in the OrderDetails Table:

OrderID | ProductID | Quantity

10248 | 11 | 12

10248 | 42 | 10

10248 | 72 | 5

etc

What I want to do is print out a list of all OrderIDs that have more than 1 product ordered.

I thought the following made sense:

Select Orders.OrderID 
   FROM Orders 
     WHERE Orders.OrderID = (Select OrderDetails.OrderID
                                  FROM OrderDetails
                                  GROUP BY OrderDetails.OrderID
                                  HAVING (COUNT(OrderDetails.ProductID) > 1))

But this is certainly not working. Only one order ID is printed even though basically ever OrderID in the database has multiple products. I hope the way I asked this wasn't too confusing, it's my first time posting about SQL on here

instead of = , if you add in, it will work, but better to use it as subquery instead of correlated query — radar

Shiva Shiva · Accepted Answer · 2014-10-24T20:02:13

This is all you need, your inner query.

Select OrderID
FROM OrderDetails
GROUP BY OrderID
HAVING COUNT(*) > 1

And if you want the other fields from the main Order table for these Orders with multiple products, then use the following.

SELECT * 
FROM Orders
WHERE OrderId IN ( 
    SELECT OrderID
    FROM OrderDetails
    GROUP BY OrderID
    HAVING COUNT(*) > 1
    )

SQL subquery and aggregate function

1 Answers