asked on

havin two select statements in on query

I am trying to get an sql procdure to work. it is the second select statement that is causing the problem. i am getting the following error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. for each record there should be only one result. is it the outer join that is causing the problem.

what i want returned
status 0 should show Order Stated
status 1 should show Order approved

(sample select statement for test)

SELECT
OrderId,
DateCreated,
DateShipped,
BillName,
CustomerId,
Status,
(select OrderStatusText
from OrdersTable od LEFT OUTER JOIN OrderStatusTable OST on OD.Status = OST.OrderStatusId) aS StatusName
FROM OrdersTable ot
where CustomerId = 'acddc51c-99c9-41db-aaed-13e96db35a3d'

Bill Prew

I would normally expect somewhere in this subquery you would need to reference one or more columns from "ot" alias, so that you get data related to the main row you are displaying. I don't know your table structure well enough to know what those fields are, but that is the problem. The subquery can only return one row as used, and is returning multiple since you haven't qualified it enough.

»bp

Bill Prew

Do you perhaps mean:

SELECT
OrderId,
DateCreated,
DateShipped,
BillName,
CustomerId,
Status,
(select OrderStatusText 
FROM OrderStatusTable OST
WHERE ot.Status = OST.OrderStatusId) aS StatusName
FROM OrdersTable ot
where CustomerId = 'acddc51c-99c9-41db-aaed-13e96db35a3d'

Open in new window

»bp

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial