[UPHPU] MySQL mess

Aaron Luman aaron.luman at gmail.com
Mon Apr 4 23:19:22 MDT 2011

I have this statement that I put together for a class project.  I've never really ventured into joins but figured since I have to do this I might as well try to learn something from it.  Anyway, I have this monstrosity of a query that I figure could be written much more succinctly.  Here we go:

select classes.class_id, classes.name, classes.spaces - ifnull(dUtC.students,0) as openings, classes.semester_id, dUtC.teacher as teacher_id, users.fname, users.lname from 
(select teachUtC.class_id as class_id, numUtC.students as students, teachUtC.user_id as teacher from 
    (select class_id, count(*) as students from users_to_classes where participation_level=4 group by class_id) as numUtC
    right join 
    (select class_id, user_id from users_to_classes where participation_level=2) as teachUtC 
on teachUtC.class_id=numUtC.class_id) as dUtC, classes, users where users.user_id=dUtC.teacher and dUtC.class_id=classes.class_id

The three tables being queried are:

 - class_id
 - info

 - user_id
 - info

 - class_id
 - user_id
 - participation_level (2 represents a teacher, 4 a student)

in the u_to_c table for every class there will be exactly one teacher entry and some number of student entries (could be 0)

this produces a table with one line for every class in the u_to_c table that has a teacher regardless of any other missing data. (the correct result)

Is there a better way to write this query?

Thanks for the help

More information about the UPHPU mailing list