Solved

Return Results Of A Subquery As A Column In A Query

Posted on 2007-04-02
5
378 Views
Last Modified: 2007-11-05
I need to run a query that returns the results of a subquery as varchar or text based column in my query. I have tried the following but get the error

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

QUERY:
select r.appt_description,
CAST((select smtp_recipient from email_smtp_recipient em
where r.rn_appointments_id = em.schedule_email_id) as varchar(8000))
as recipients
from rn_appointments r
where r.rn_appointments_id = '0FE9F3'

How do I do this? Thanks!!
0
Comment
Question by:pauldes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18837542
The problem occurs because the subselect is returning smtp_recipients for every appointment, not just the one of interset.  You could add an appointment_id criteria to the subselect, but I think this is better done with a join. Try this:

select r.appt_description,
CAST(em.smtp_recipient as varchar(8000) as recipients
from rn_appointments r
--left join mimics your original attempt, but you may really want an inner join---I just don't know
left join email_smtp_recipient em on r.rn_appointments_id = em.schedule_email_id  
where r.rn_appointments_id = '0FE9F3'
0
 

Author Comment

by:pauldes
ID: 18837612
Yes, thanks.....but that would return a unique row for each record where r.rn_appointments_id = '0FE9F3'.
I need all the values of em.smtp_recipient dumped into a SINGLE field as part of the query. So my query should return 1 row with two fields, the second field returning all the records in the child table into that one field.

say appt_description = 'How Are You?' and smtp_recipient.email_smtp_recipient returned 2 rows, 'joe@abc.com' and 'bob@abc.com', my query should return a single row like:

Col001                Col002
'How Are You?'  'bob@abc.com,joe@abc.com'

Sorry if I wasn't clear.......
0
 
LVL 3

Expert Comment

by:ia2189
ID: 18837704
If you want all the rows in a single row then you'd probably have to do something like a cursor to loop through all the values and then place them in the column for the single row.  It would look something like what I have below.  A cursor is very similar to a while...loop.

declare @tmpEmail as varchar(8000)
declare @Email as varchar(8000)

set @tmpEmail = ''
set @Email = ''

declare curEmail as cursor for select smtp_recipient from email_smtp_recipient
open curEmail
fetch next from curEmail into @tmpEmail

while @@fetch_status = 0 begin
    set @Email = @Email + @tmpEmail + ','
    fetch next from curEmail into @tmpEmail
end

close curEmail
deallocate curEmail

select r.appt_description, substring(@Email, 1, len(@Email)-1) as recipients
from rn_appointments r
where r.rn_appointments_id = '0FE9F3'
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18837754
I use a function instead:

CREATE FUNCTION dbo.GroupEmails ( @id int )
returns varchar(8000)
as
begin
  declare @res varchar(8000)
  select @res = coalesce( @res + ',' , '' ) + smtp_recipient
  from email_smtp_recipient
  where schedule_email_id = @id
  return (@res)
end


and use it like this:
select r.appt_description, dbo.GroupEmail(r.rn_appointments_id) recipients
from rn_appointments r
where r.rn_appointments_id = '0FE9F3'


0
 

Author Comment

by:pauldes
ID: 18838350
Nice......

Thanks
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question