For whatever reason, I'm not grasping the logic I need so I'm looking for some insight.
I have a database that tracks incoming students. One table (responses) includes the student's general information and another (major) tracks the areas of study he/she is interested in learning. So I would have one record in resonses table but could have multiple records in the major table. The link is the ID--responses.ID is unique and major table includes major.majorID for each row which is really responses.ID.
Now the output is where I'm having issues. I have a left outer join running (because the student could have no major of interest).
Code:
<cfquery name="getResults" datasource="#myds#">
SELECT *
FROM responses r LEFT OUTER JOIN sai_major m ON m.majorID = r.ID
ORDER BY r.response_date
</cfquery>
I need to output this in an excel-style layout. Each record will have it's own row, but I want the associated majors to appear in as a list in one cell. Of course if I just do a straight query, I get 1 record output as many times as the major it's attached to.
My thought is to have <cfoutput query="getResults" group="ID"> and then loop through the list later. But is that the best/fastest way? And I need whatever speed I can get. I feel like I'm missing something obvious.
Edited to add:
I forgot to mention that there is a second child table added to the mix (my brain is so not in my head right now). So, in another area, I will have all three combined as such.
Code:
<cfquery name="getResults" datasource="#Application.myds#">
SELECT *
FROM (responses r LEFT OUTER JOIN major m ON m.majorID = r.ID) LEFT OUTER JOIN preference p ON p.prefID = r.ID
ORDER BY r.response_date
</cfquery>
Dev Shed Forums Sponsor:
June 9th, 2005, 10:51 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
