pauldes
asked on
Return Results Of A Subquery As A Column In A Query
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!!
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!!
ASKER
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.......
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_
Col001 Col002
'How Are You?' 'bob@abc.com,joe@abc.com'
Sorry if I wasn't clear.......
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'
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nice......
Thanks
Thanks
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'