?
Solved

Return Results Of A Subquery As A Column In A Query

Posted on 2007-04-02
5
Medium Priority
?
379 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 2000 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 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