CODEWITHSUNDEEP
sqlsql-server
Andrew Caprario
Andrew Caprario

Reputation: 92

Subquery is returning more than one value

SELECT TOP 100 PERCENT  
    dbo.Customers.Company, 
    MAX(dbo.Orders.ShipDate) AS Expr1,
    (SELECT dbo.OrderItems.Price FROM dbo.OrderItems) AS Expr2
FROM 
    dbo.Customers
INNER JOIN 
    dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID
INNER JOIN 
    dbo.OrderItems ON dbo.Orders.OrderID = dbo.OrderItems.OrderID
WHERE 
    (dbo.Orders.Deleted = 0)
GROUP BY 
    dbo.OrderItems.InvMasID, dbo.Customers.Company

I am trying to pull the price from dbo.OrderItems.Price that is associated with the correlating dbo.Orders.ShipDate that is called in the outer query. My query keeps throwing an error saying that the inner query is returning more than 1 value.

My question is how would I go about having the inner query pull only the values matching the what the outer query is pulling.

Upvotes: 0

Views: 69

Answers (2)

ColinMac
ColinMac

Reputation: 640

If you explain which customer order you want, and whether you want the whole order price or the price of each item in that order, we can be more specific. I had to make some assumptions (see comments):

select
      c.Company
    , o.ShipDate as this_is_max_ship_date_of_customer 
    , o.OrderID --Added OrderID so you know what the price is associated with
    , sum(oi.price) this_is_sum_of_prices_of_the_order
                --if you want the price of a particular item, you can add ItemID beneath OrderID and also group by it
from dbo.Customers c
join dbo.Orders o
    on c.CustomerID = o.CustomerID
join dbo.OrderItems oi
    on o.OrderID = oi.OrderID
where o.Deleted = 0
    and o.ShipDate in (select max(inner_o.ShipDate) from dbo.Orders inner_o
                       where inner_o.Company = c.Company
                       group by inner_o.Company)
                       --This subquery points to the outer reference - a useful trick
                       -- it means only the most recent ShipDate for the customer is shown
    and o.OrderId in (select max(inner_o2.OrderId) from dbo.Orders inner_o2
                       where inner_o2.Company = c.Company
                         and inner_o2.ShipDate = o.ShipDate
                       group by inner_o2.Company)
                       --This subquery is doing the same thing, but for your OrderId
                       --Unfortunately you don't specify enough in your question, so
                       --  I have to assume you only want their most recent order
                       --To prevent an ShipDate that is not of the OrderId you care about,
                       --  I had to add the second where clause here
group by
    c.Company,
    o.ShipDate,
    o.OrderId

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You need to decide how you want to combine all the different prices on the orders for a company. I don't know what you want. For instance, the following returns the sum of all the prices:

SELECT c.Company, MAX(o.ShipDate) AS Latest_ShipDate,
       SUM(oi.Price) AS sum_Price
FROM dbo.Customers c JOIN
     dbo.Orders o
     ON c.CustomerID = o.CustomerID JOIN
     dbo.OrderItems oi
    ON o.OrderID = oi.OrderID
WHERE o.Deleted = 0
GROUP BY oi.InvMasID, c.Company

Upvotes: 1

Related Questions