Reputation: 53
i want to Display the OrderID of all orders that where placed after all orders placed by Bottom-Dollar Markets.
i used the fllowing query
select
Orders.OrderID
from
Orders
where
Orders.OrderDate > (
select
Orders.OrderDate
from
Orders
where
Orders.CustomerID = (
select
Customers.CustomerID
from
Customers
where
Customers.CompanyName='Bottom-Dollar Markets'
)
);
but it gives the error subquery returened more than one value
i am using northwind database
Upvotes: 2
Views: 5308
Reputation: 50855
Both of your subqueries could return multiple rows, which isn't allowed when you're using a scalar comparison operation - >
and =
, respectively, in your case.
Try this instead:
select Orders.OrderID
from Orders
where Orders.OrderDate > (
select max(Orders.OrderDate)
from Orders
where Orders.CustomerID in (
select Customers.CustomerID
from Customers
where Customers.CompanyName='Bottom-Dollar Markets'
)
);
You don't actually need to compare all orders since if the order of a customer is greater than the latest order placed by Bottom-Dollar Markets, then it follows it is also greater than earlier orders.
Alternatively this would work with a JOIN
:
select Orders.OrderID
from Orders
where Orders.OrderDate > (
select max(Orders.OrderDate)
from Orders join Customers on Orders.CustomerID = Customers.CustomerID
where Customers.CompanyName='Bottom-Dollar Markets'
);
Upvotes: 6