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 |
|
pavlos
Yak Posting Veteran
59 Posts |
 Posted - 2010-05-23 : 03:17:08
|
Hey Guys,I run my code and get this error messageMsg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.rather than posting my whole codeand table structure i am trying to say where an id = another id of the subquery but the subquery returns many id.sthis is an exampleselect distincts.schoolnamefrom school as swhere s.schoolname like 'primary %'ands.personid =(selectp.personidfrom people as pwhere p.suburb like 'athens')[code]the above code works and produces a list of school names and the subquery works and produces a list of idsi want it to return the names of all the schools where those ids exist in the schoolso my error is obviously coming from here[code]s.personid =( etc. etc.this is because my subquery is returning many results. How do i write it in a way so it will operate and return the many names of a school.hope this makes sense.english is my second language.cheers |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-05-23 : 03:31:49
|
Use EXISTS instead like this:WHERE s.schoolname LIKE 'primary %' AND EXISTS(SELECT p.personid FROM people AS p WHERE p.personid = s.personid AND p.suburb LIKE 'athens') |
 |
|
|
pavlos
Yak Posting Veteran
59 Posts |
Posted - 2010-05-23 : 07:48:55
|
hey guys,worked it out!i did Where p.personid IN when using exists it would bring up all of the schools that were primary schools |
|
|
|
|
|
|
equals subquery with many rows