On Apr 4, 2011, at 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?
To do *just* this (one line per class):
select
*
from
users_to_classes
left join classes on (users_to_classes.class_id = classes.class_id)
where
users_to_classes.participation_level = 2
The "left" is only necessary if there could be missing records in the classes
table; not sure whether that can happen in your scenario. I'm doing a lot of
left joins in this email because I'm not sure what "missing data" you're
referring to.
However, your query does more. If you want the teacher's name, you'll also need
to join the users table. This should return a line for each class in the u_to_c
table plus the corresponding class and user records, IF they exist:
select
*
from
users_to_classes
left join classes on (users_to_classes.class_id = classes.class_id)
left join users on (users_to_classes.user_id = users.user_id)
where
users_to_classes.participation_level = 2
To add the enrollment data, join on that subselect you created:
select
*
from
users_to_classes
left join classes on (users_to_classes.class_id = classes.class_id)
left join users on (users_to_classes.user_id = users.user_id)
left join (
select
class_id, count(*) as students
from
users_to_classes
where
participation_level = 4
group by
class_id
) as numUtC on users_to_classes.class_id = numUtC.class_id
where
users_to_classes.participation_level = 2
I've left the select statements as an exercise for the reader...
Jenn
>
> Thanks for the help
> Aaron
>
> _______________________________________________
>
> UPHPU mailing list
> [email protected]
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
