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
