Forum OpenACS Q&A: Response to There's a problem with education/class/one.tcl

O.k. I admit it. I ported the original query, and yes it is wrong. When I first ported it, I didn't quite understand how to handle those on-the-fly views, and in this case I got it wrong. The approch here was to convert the on-the-fly views to actual views. The problem with doing that is the on-the-fly views use tcl variables that aren't known until run-time. To solve that problem I created partial views that mimicked the original on-the-fly views and added the remaining where clauses into the where clause for the main query. This works o.k., but in the case of this query I didn't compensate each of the sub-clauses that use a not exits statement to emulate the outer-joins. The main problem was that I added "and answers.student_id = $user_id" to each of the unioned sub-selects. This and clause needs to be removed for the sub-clauses that use not exists on the student_answers table. I've tried to post the corrected query, but I keep getting a dml error message.