I cant even follow you. What your looking for is :
one line for every class in the u_to_c table that has a teacher Why not SELECT * FROM class LEFT JOIN u_to_c ? On 4/4/2011 11:19 PM, Aaron Luman wrote: > 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: > > classes: > - class_id > - info > > users: > - user_id > - info > > users_to_classes: > - 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 > Aaron > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net -- Trevyn _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
