Help Needed For Complex Inner Join In Coldfusion

Hi,

I'm having a problem retrieving accurate information from an inner join. I am trying to access three database tables for this information.

1. GRADES, which contains Subcategory id (SC_ID), Student id (STU_ID), and grade (GRADE)

2. SUBCATEGORY, which contains Subcategory id (SC_ID), Category id (CAT_ID), and Course id (COURSE_ID)

3. CATEGORY, which contains Category id (CAT_ID) and Course id (COURSE_ID)

I know how to retrieve this information using subqueries but ColdFusion doesn't support subqueries (darn!!) and I am forced to use inner joins. But I am at a complete loss on how to accomplish this. I have been trying different combinations for hours (trust me) and I still can't find a solution. The result I am looking for is to have the query retrieve the correct category name using the course number provided from the previous form, use this category name to find the correct subcategory ids, and find the correct grades using this subcategory id.

Here is the code with the subquery:

<!-- the course number passed from previous form -->

<cfif not isDefined("number")>
<cflocation url="report.cfm">
</cfif>

<!-- retrieves an array of students belonging to the course number -->
<cfset myArray = ArrayNew(2)> <sets up a 2-d array>
<cfquery name="getstudents" dataSource="ternion01">
select STUDENT.STU_ID, STU_LNAME, STU_FNAME from ENROLLMENT, STUDENT where ENROLLMENT.COURSE_ID = "#number#" AND STUDENT.STU_ID = ENROLLMENT.STU_ID
</cfquery>
<!-- retrieves the student information and places this info in array -->
<cfloop query="getstudents">
<cfset myArray[CurrentRow][1] = getstudents.STU_ID>
<cfset myArray[CurrentRow][2] = getstudents.STU_LNAME>
<cfset myArray[CurrentRow][3] = getstudents.STU_FNAME>
</cfloop>

<!-- attempting to query the database for specific categories and their corresponding subcategory grades -->
<!-- using a loop to search for information based on the approprate student defined in the array above -->
<!-- this is where the problem is!! -->
<cfloop index="i" from="1" to="#getstudents.RecordCount#">
<cfquery name="getdata" dataSource="ternion01">
select CATEGORY.CAT_NAME, SUM(GRADE)*(CAT_WEIGHT/100) MYSUM
from CATEGORY, GRADES
where CATEGORY.COURSE_ID = "#number#"
AND STU_ID = "#myArray[i][1]#"
AND GRADES.SC_ID = (select SC_ID from SUBCATEGORY where CAT_ID = (select CAT_ID from CATEGORY where COURSE_ID = "#number#"))
group by CAT_NAME

</cfquery>
<!-- the problem is with the subquery, I am clueless on how to join all this information --> <!-- places this information into the array with the student information -->
<cfloop query="getdata">
<cfset myArray[i][(CurrentRow*2)+2] = getdata.CAT_NAME>
<cfset myArray[i][(CurrentRow*2)+3] = getdata.MYSUM>
</cfloop>
</cfloop>

<!-- used to check contents of the array -->
<cfdump var="#myArray#">

Any help will be much appreciated.

Thanks,
Cassandra

Cassandra, you are mistaken. When it comes to SQL, ColdFusion handles nothing. All ColdFusion does is forward the SQL statement to a database via a data source connection. If you are having problems getting subqueries to work, it is either a mistake in your code or the fact that your RDBMS does not support subqueries. ColdFusion has nothing to do with it. I have built queries with multiple inline views and multiple levels of nested subqueries to query Oracle 9i databases.

So, if you feel that using subqueries would solve your problem, I'd look into that before trying to cludge around it with inner joins.

cassjack, please do not cross-post

http://forums.devshed.com/showthread.php?t=142412

which one of these two threads do you want to keep? i will delete the other one