Return Multiple Values via Subquery

G

Guest

I need a column in my query to display a list of values returned from a
subquery. No problem if the subquery only returns a single value, or if I
alter it to return an aggregate value like COUNT. But I get the message "At
most one record can be returned by this subquery" when I try to have it
return multiple values.

There are never more than 3 matches (but there could be 0, 1, 2 or 3
matches), and what I really need is to return a comma-separated list of the
text values found by the subquery (like "Name1, Name2, Name3") as a string or
text value of some sort (any type of delimiting should suffice).

The subquery is defined as the FIELD value for the column in my primary
query, and looks like the following:

OtherServiceLines: (SELECT [ServiceLines].[ServiceLineName]
FROM [ServiceLines]
WHERE [CurrentID] = [ServiceLines].[Sales_ID])

Any ideas on how to get a column to contain a list of values obtained via a
subquery or is there another method to accomplishing this madness???

As always, THANKS for all your help!
 
P

Peter Yang [MSFT]

Hello,

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 Server displays an error message.

Subqueries with Comparison Operators
http://msdn2.microsoft.com/en-us/library/ms190609(en-us,VS.90).aspx

I think you could use a function or stored proc to achieve this goal. The
following link might be helpful

http://www.aspfaq.com/show.asp?id=2529

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
Thread-Topic: Return Multiple Values via Subquery
thread-index: AcYRyz01FXg6O5+PQf2mlQ5K8H8Nxg==
X-WBNR-Posting-Host: 192.85.50.1
From: "=?Utf-8?B?SkRSYXZlbg==?=" <[email protected]>
Subject: Return Multiple Values via Subquery
Date: Wed, 4 Jan 2006 23:40:02 -0800
Lines: 22
Message-ID: <[email protected]>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.access.queries
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.access.queries:263378
X-Tomcat-NG: microsoft.public.access.queries

I need a column in my query to display a list of values returned from a
subquery. No problem if the subquery only returns a single value, or if I
alter it to return an aggregate value like COUNT. But I get the message "At
most one record can be returned by this subquery" when I try to have it
return multiple values.

There are never more than 3 matches (but there could be 0, 1, 2 or 3
matches), and what I really need is to return a comma-separated list of the
text values found by the subquery (like "Name1, Name2, Name3") as a string or
text value of some sort (any type of delimiting should suffice).

The subquery is defined as the FIELD value for the column in my primary
query, and looks like the following:

OtherServiceLines: (SELECT [ServiceLines].[ServiceLineName]
FROM [ServiceLines]
WHERE [CurrentID] = [ServiceLines].[Sales_ID])

Any ideas on how to get a column to contain a list of values obtained via a
subquery or is there another method to accomplishing this madness???

As always, THANKS for all your help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top