June 20, 2012 at 7:44 am
I am having a problem with my query and subquery within the query. I get and error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
select cp_proctor, cp_totalStrikes, isnull((select USO_StrikeNo from UsersStrikeOuts where ID_Users = 47505 and CourseID = 4058), 0) as totalStrikes from companyProfile where id_cp = 719
June 20, 2012 at 7:56 am
The problem is that this:
select USO_StrikeNo
from UsersStrikeOuts
where ID_Users = 47505 and CourseID = 4058
returns more than 1 value.
My "guess" without knowing your table structures and their relationships is that it should be more like this:
select cp.cp_proctor, cp.cp_totalStrikes, isnull(uso.USO_StrikeNo, 0) as totalStrikes
from companyProfile cp
left join UsersStrikeOuts uso
on cp.cp_proctor = uso.cp_proctor --or whatever the join condition is
where cp.id_cp = 719
If you require more help, you will have to provide DDL for the 2 tables and some sample data (does not have to be real, just fake similar). Please look at the article in my signature by Jeff Moden if you require help on posting this.
Jared
CE - Microsoft
June 20, 2012 at 8:39 am
vanessachse81 (6/20/2012)
I am having a problem with my query and subquery within the query. I get and error:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
select cp_proctor, cp_totalStrikes, isnull((select USO_StrikeNo from UsersStrikeOuts where ID_Users = 47505 and CourseID = 4058), 0) as totalStrikes from companyProfile where id_cp = 719
Jared is most likely right, but in my experience subqueries like that are only used when there are multiple rows that will be matched in the target table that you want to avoid. In which case a straight join won't do the job. A typical example is when trying to pull the latest value from a history table. In which case, your subquery needs a Top 1 clause in followed by an order by. Something like the below.
SELECT CustomerID, CustomerName,
(SELECT TOP 1 PaymentAmount
FROM PayHistoryTable
WHERE CustomerID = c.CustomerID
ORDER BY PaymentDate Desc) as LastPaymentAmount
FROM CustomerTable c
However this peforms very poorly in large result sets.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply