Insufficient output from SQL query

Question

I'm using the northwind db: http://dev.assets.neo4j.com.s3.amazonaws.com/wp-content/uploads/Northwind_diagram.jpg

I have to output all orders placed by CustomerID ALFKI with more than one unique product. I get the correct orders out, but I can't figure out why it's only printing one product name per order.

My query:

SELECT a.OrderID, p.ProductName
FROM Products p 
INNER JOIN 'Order Details' a 
  ON (p.ProductID = a.ProductID) 
INNER JOIN Orders b 
  ON (a.OrderID = b.OrderID) 
WHERE (b.CustomerID = 'ALFKI') 
GROUP BY a.OrderID 
HAVING COUNT(DISTINCT a.ProductID) > 1

Answer 1

You need the GROUP BY and HAVING to be part of a subquery, with your primary query selecting the detail using the list of OrderIDs returned from the subquery as filter criteria. Try the following syntax for T-SQL:

SELECT 
    a.OrderID, 
    p.ProductName
FROM 
    Products p 
    INNER JOIN [Order Details] a 
        ON (p.ProductID = a.ProductID) 
    INNER JOIN Orders b 
        ON (a.OrderID = b.OrderID) 
WHERE 
    a.OrderID IN 
    (
        SELECT a.OrderID
        FROM [Order Details] a 
        INNER JOIN Orders b 
            ON (a.OrderID = b.OrderID) 
        WHERE (b.CustomerID = 'ALFKI') 
        GROUP BY a.OrderID 
        HAVING  COUNT(DISTINCT a.ProductID) > 1
    )