Re: Select subquery with joins

[Phillip Ward]

This kind of "Is is this? Is it that? Is it something else?" thinking is horrendously inefficient and, as you've discovered, a maintenance nightmare.
Ask the database to give you the data that's there and then deal with whatever you get back in your application layer - that's what it's for.

SELECT    u.firstname 
,         u.lastname 
,         u.email 
,         ue.courseid 
from      mdl_users u 
left join mdl_user_enrolements ue 
     on   u.id = ue.userid 
WHERE     u.deleted='0' 
order by  u.id ;

Yes, you get some duplication of data when a student is on more than one course, but if you really don't want that, then you need two selects - one to get the user data, another to get the courses and mash the two together in the application.

Regards, Phill W.