CODEWITHSUNDEEP
sqlsql-server-2008-r2
barrbarry
barrbarry

Reputation: 13

returned more than 1 value error

I have issued with my sql server query. When I run this code, I get an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have tried a many ways but it still errors - how can I solve it?

Upvotes: 1

Views: 148

Answers (1)

Bernhard Barker
Bernhard Barker

Reputation: 55619

The error is pretty straight-forward. Your subquery returns more than 1 row. This is not allowed because of how queries work. Things in the SELECT clause cannot define multiple rows, this must be in the FROM clause.

There are many ways to solve this, some of them:

SELECT (SELECT MAX(field) ...)
SELECT (SELECT field ... GROUP BY field)
SELECT (SELECT TOP 1 field ...)

The problem is that all of the above (with the possible exception of the GROUP BY, which may not work) will quite possibly return unwanted data - only a single row for each row in the original query.

You may want to use XML PATH to concatenate all these values into a single cell.

Or if you're happy with multiple rows, use JOIN instead:

SELECT someAlias.field
...
JOIN table as someAlias ON someAlias.somefield = someothertable.someotherfield

As in:

SELECT A.MR_EMPLOYEE_CHECKROLL_TYPE_ID
FROM   T_PR_CHECKROLL_WISE_LOG
       JOIN T_TR_CLOSING
         ON T_TR_CLOSING.CLOSING_ID = T_PR_CHECKROLL_WISE_LOG.CLOSING_ID
       JOIN T_PR_EMPLOYEE_LOG
         ON T_PR_EMPLOYEE_LOG.CLOSING_ID = T_TR_CLOSING.CLOSING_ID
       JOIN T_PR_EMPLOYEE_LOG AS A
         ON A.EMP_ID = T_PR_CHECKROLL_WISE_LOG.EMP_ID
WHERE  T_PR_CHECKROLL_WISE_LOG.CLOSING_ID = '1'

Upvotes: 4

Related Questions