Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
csphard
Posting Yak Master
113 Posts |
 Posted - 2009-01-06 : 19:33:54
|
| what is wrong with this statement select empid from testwhere empid <> (select empid from ptest)I get the error message Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.I can not see what is wrong.Howard |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DeveloperIQ
Yak Posting Veteran
71 Posts |
Posted - 2009-01-06 : 19:44:15
|
| Use a "NOT IN" instead of <> |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-01-06 : 19:44:38
|
| this is because on the left of the inequality, you have one value, namely, empid, but the query on the right side of the inequality returns multiple values. T-SQL is unable to compare a single value against the multiple values.What you probably want is something like this:select empid from test t where not exists (select * from ptest p where p.empid = t.empid) |
|
|
|
csphard
Posting Yak Master
113 Posts |
Posted - 2009-01-06 : 20:25:48
|
| Thank you for your help.Howard |
|
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-01-06 : 22:47:56
|
| SubQuery Returns Only one row, If u have more than one row in the subquery result then error occurs in the statement.Tkizer suggested the correct one for ur requirment. |
|
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2009-01-06 : 23:20:54
|
| Use NOT EXISTS Rather than NOT IN For better PerformanceJai Krishna |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Can not see what is wrong with statement