You want to write a query that gives you this
buildingname teacherID teacherID TeacherName
High School 1 1 Bob
Middle School 1 1 Bob
EMC 1 1 Bob
ELC 2 2 Sue
Middle School 2 2 sue
group it by teachername
and do somethinglike
<cfoutput group ="teacher">
#TeacherName#
<cfoutput>
#buildingname#
</cfoutput>
</cfoutput>
WG
-----Original Message-----
From: Mark W. Breneman [mailto:[EMAIL PROTECTED]
ia.net]
Sent: 18 July 2003 16:27
To: CF-Talk
Subject: Concatenate results of subquery in select
Yes, I know this is not standard practice but, I am working on a report and
I would like all of the buildings this teacher teaches at to be in a single
CF query column.
I hope to get query results like:
TeacherName Buildings
Bob High School, Middle School, EMC
Sue ELC, Middle School
Here is basically what I am trying to do:
SELECT TeacherName, (select buildingname from location where teacherID = 1)
as Buildings
FROM Teachers
But you can not run a subquery that returns more then one value.
Any Ideas on how to work around this?
Tables look like
Teachers table:
teacherID TeacherName
1 Bob
2 Sue
Location table:
buildingname teacherID
High School 1
Middle School 1
EMC 1
ELC 2
Middle School 2
I am running:
MS SQL server 2000
CFMX
Mark W. Breneman
-Macromedia Certified ColdFusion Developer
-Network / Web Server Administrator
Vivid Media
[EMAIL PROTECTED]
www.vividmedia.com
608.270.9770
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community.
http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
