CODEWITHSUNDEEP
sql-serversubquery
user6656728
user6656728

Reputation:

SQL Server:subquery returns more then 1 value

i have two tables table one contains

a b c d e f

and table two contains

b c d e f g h i j k l

i want to show the data from table two and the data should not be used in table one (k l) here the value k and l should be printed

in my example i have written the query

select comarea from companyarea where comarea !=(select area from companyallot where comname='24' and zone='west' and location='mumbai')

but the error is coming

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

here in my example i want to use the area which are not being used in the table called companyallot

Upvotes: 0

Views: 899

Answers (4)

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

Replace != with NOT IN operator

SELECT COMAREA FROM COMPANYAREA WHERE COMAREA NOT IN  (
        SELECT AREA
        FROM COMPANYALLOT
        WHERE COMNAME = '24'
            AND ZONE = 'WEST'
            AND LOCATION = 'MUMBAI'
        )

Upvotes: 1

User
User

Reputation: 804

In Where condition "=" operator will deal with single value, "in" operator will deal with multiple values. So instead of using != use "not in" operator as below.
SELECT comarea FROM companyarea 
     WHERE comarea NOT IN
     (SELECT area FROM companyallot WHERE comname='24' AND zone='west' AND      
     location='mumbai')

Upvotes: 2

GeeDee
GeeDee

Reputation: 11

You need to do two things in order to make it work:

-> Used a multi-value operator NOT IN (OR) IN

-> Although your case does not specify it, but it is always good if you treat NULLS well in advanced, by using NVL in your inner subquery results.

-> NOT EXISTS Operator should still work

Upvotes: 0

M.Ali
M.Ali

Reputation: 69504

NOT IN operator works fine as long as there is no NULL value being returned by the sub-query but it all goes pear shape as soon as there is even one null value in the sub-query.

A safer option would be to use EXISTS/NOT EXISTS operator, something like... 

select a.comarea 
from companyarea a
where NOT EXISTS (select 1 
                 from companyallot t 
                 where t.comname='24' 
                   and t.zone='west' 
                   and t.location='mumbai'
                   and a.comarea = t.area)

Upvotes: 0

Related Questions