🚀 go-pugleaf

Read this atricle from Books Online.

Regards,
Vassiliev V.V.
http://www.oledbdirect.com

Subqueries with Comparison Operators
Subqueries can be introduced with one of the comparison operators (=, < >,
>, > =, <, ! >, ! <, or < =).
A subquery introduced with an unmodified comparison operator (a comparison
operator not followed by ANY or ALL) must return a single value rather than
a list of values, like subqueries introduced with IN. If such a subquery
returns more than one value, Microsoft� SQL ServerT displays an error
message.
To use a subquery introduced with an unmodified comparison operator, you
must be familiar enough with your data and with the nature of the problem to
know that the subquery will return exactly one value.
For example, if you assume each publisher is located in only one city, and
you want to find the names of authors who live in the city in which Algodata
Infosystems is located, you can write a statement with a subquery introduced
with the simple = comparison operator.
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE city =
   (SELECT city
   FROM publishers
   WHERE pub_name = 'Algodata Infosystems')

Here is the result set:
au_lname      au_fname
--------      --------
Carson      Cheryl
Bennet      Abraham

(2 row(s) affected)

If, however, Algodata Infosystems was located in multiple cities, then an
error message would result. Instead of the = comparison operator, an IN
formulation could be used (= ANY also works).
Subqueries introduced with unmodified comparison operators often include
aggregate functions, because these return a single value. For example, this
statement finds the names of all books priced higher than the current
minimum price.
USE pubs
SELECT DISTINCT title
FROM titles
WHERE price >
   (SELECT MIN(price)
   FROM titles)

Here is the result set:
title
----------------------------------------------------------
But Is It User Friendly?
Computer Phobic and Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Emotional Security: A New Algorithm
Fifty Years in Buckingham Palace Kitchens
Is Anger the Enemy?
Life Without Fear
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Prolonged Data Deprivation: Four Case Studies
Secrets of Silicon Valley
Silicon Valley Gastronomic Treats
Straight Talk About Computers
Sushi, Anyone?
The Busy Executive's Database Guide

(14 row(s) affected)

Because subqueries introduced with unmodified comparison operators must
return a single value, they cannot include GROUP BY or HAVING clauses unless
you know the GROUP BY or HAVING clause itself returns a single value. For
example, this query finds the books priced higher than the lowest priced
book that has a type 'trad_cook'.
USE pubs
SELECT DISTINCT title
FROM titles
WHERE price >
   (SELECT MIN(price)
   FROM titles
   GROUP BY type
   HAVING type = 'trad_cook')

Here is the result set:
title
------------------------------------------------------------------------
But Is It User Friendly?
Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Prolonged Data Deprivation: Four Case Studies
Secrets of Silicon Valley
Silicon Valley Gastronomic Treats
Straight Talk About Computers
Sushi, Anyone?
The Busy Executive's Database Guide

(9 row(s) affected)

�1988-2000 Microsoft Corporation. All Rights Reserved.

"Emilio Rodriguez" <sistemas@acisa.es> ???????/???????? ? ????????
?????????: news:3D2158A0.8033A3D6@acisa.es...
> Hello,
>
> I'm doing a programs with a database MSDE(SQL Server). I attack the
> database with ADOConection and ADODataSet. When I filter or conecct
> sometimes the programs throw this exceptions: "Subquerry returned more
> than 1 value. This is not permited when the subquerry follow
> =,!=,<,<=,>,>= or when the subquerry is used as and expresion". Someone
> know why the programs throw this exception?
>
> Thank you for all,
> Emilio
>