[UPHPU] MySQL mess

Jennifer Wollesen jwollese at gmail.com
Tue Apr 5 11:42:50 MDT 2011


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
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net



More information about the UPHPU mailing list