Multi-table SQL select statement & cfoutput syntax

Question

I teach web design to high school students. Recently I introduced them to dynamic web design with SQL and coldfusion. I have been keeping it rather simple, but I do want to show them a complex SQL statement and a complex cfoutput. Unfortunately I have made the problem too complex for me to figure out! Here is a pic of my database structure:

I am trying to output a list of students enrolled in each class along with the session the class is offered, and the teacher who teaches the class. Here is an example of what I am trying to output

Robotics
Session 1
Mr. Smith

1. Fry, Karen
2. Johnson, Johnny
3. Turner, Haley

And then I want to output the information for the next class, and so on.

Here is the cfoutput code I am using:

<cfoutput query="getRoster" group="staff">
#firstname# #lastname#<br>
#className#<br>
#sessionNumber#<br>
#title# #lastname#<br>
<cfoutput>
`<ol><li>#lastname#, #firstname#</li></ol>`
</cfoutput>
</cfoutput>

Answer 1

Assuming your query returns the correct data already, you're close. The query just needs a few changes:

1. Since the "firstName" and "lastName" columns exist in multiple tables, the query must apply a different column alias to each one, ie SELECT student.lastName AS StudentLastName, ..... Otherwise, CF won't know which value to output when it sees #firstName# and #lastName#.
   

2. The cfoutput should probably "group" by className, instead staff. Otherwise, it won't display all of the class names if the same staff member is assigned to multiple classes.

3. CFOutput "group" requires query results to be sorted. Be sure the query is ORDER(ed) BY className (or whatever column you're grouping by) or the cfoutput won't display correctly.

4. In order to generate list numbers, the <ol> element belongs outside the inner <cfoutput> loop.

Query:

SELECT
        c.className
        , c.sessionNumber
        , s.LastName AS StaffLastName
        , s.FirstName AS StaffFirstName
        , st.LastName AS StudentLastName
        , st.FirstName AS StudentFirstName
FROM   class c 
        INNER JOIN staff s ON s.id = c.staff
        INNER JOIN studentOld st ON st.class1 = c.id
            OR st.class2 = c.id 
            OR st.class3 = c.id 
            OR st.class4 = c.id 
ORDER BY c.className, st.LastName, st.FirstName

CF

<cfoutput query="getRoster" group="className">
    #StaffTitle# #StaffLastName# #StaffLastName#<br>
    #className#<br>
    #sessionNumber#<br>
    <ol>
        <cfoutput>
            <li>#StudentLastName#, #StudentFirstName#</li>
        </cfoutput>
    </ol>
</cfoutput>

Improvements

That said, if possible I'd restructure the student table as that's not the typical way to represent the many-to-many relationship that exists between student and class. (Best to reinforce good database structure habits now!) A cleaner and more normalized approach is to remove the classX columns from the student table. Then store those relationships in a separate table - as individual rows - rather than columns. That allows the flexibility for as many, or as few, enrollments as needed.

CREATE TABLE student (
id int
, lastName varchar(100)
, firstName varchar(50)
)

-- stores each combination of student + class as a separate row
CREATE TABLE studentClass (
student int
, class int
)

Then you can get rid of all the OR statements and retrieve the classes and enrolled students, with a slightly cleaner JOIN:

SELECT ...
FROM   class c 
        INNER JOIN staff s ON s.id = c.staff
        INNER JOIN studentClass sc ON sc.class = c.id
        INNER JOIN student st ON st.id = sc.student
ORDER BY ....