Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return Results Of A Subquery As A Column In A Query

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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