Solved

Return Results Of A Subquery As A Column In A Query

Posted on 2007-04-02
5
376 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
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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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