Solved

Return Results Of A Subquery As A Column In A Query

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 35
sql 2014,  lock limit 5 32
Simple SQL query from two tables 13 54
Passing Parameter to Stored Procedure 4 24
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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…
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…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

770 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