Olá pessoal, estou precisando de um select q retornem todos os usuários de um curso que estão sem grupo.
Se possível sugiro aqui criarmos um forum ou tópico apenas de selects úteis, exemplo:
Select de todos os cursos.
Select para pegar todos o alunos de um curso e assim por diante.
Peguei aqui o select que faz issso...
mas ainda fica a idéia de criarmos um fórum ou tópico somente sobre selects uteis...
--- dados de todos os alunos que estão em uma sala ---
select u.username, u.firstname, u.lastname, u.email, u.city from ((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id)
where co.id = 49 and r.id = 5
--------------------------------------------------------
--- dados dos alunos que estão nos grupos de uma sala ---
select u.username, u.firstname, u.lastname, u.email, u.city, g.name from ((((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id) left join mdl_groups_members as gm on gm.userid = u.id)
left join mdl_groups as g on gm.groupid = g.id)
where co.id = 89 and co.id = g.courseid and r.id = 5 order by g.name
--------------------------------------------------------
--- dados de alunos que não estão em nenhum grupo de uma sala ---
select u.username, u.firstname, u.lastname, u.email, u.city from (((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id) left join mdl_groups_members as gm on gm.userid = u.id)
where co.id = 89 and r.id = 5 and u.id not in (select u.id from ((((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id) left join mdl_groups_members as gm on gm.userid = u.id)
left join mdl_groups as g on gm.groupid = g.id)
where co.id = 89 and co.id = g.courseid and r.id = 5)
-------------------------------------------------------
mas ainda fica a idéia de criarmos um fórum ou tópico somente sobre selects uteis...
--- dados de todos os alunos que estão em uma sala ---
select u.username, u.firstname, u.lastname, u.email, u.city from ((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id)
where co.id = 49 and r.id = 5
--------------------------------------------------------
--- dados dos alunos que estão nos grupos de uma sala ---
select u.username, u.firstname, u.lastname, u.email, u.city, g.name from ((((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id) left join mdl_groups_members as gm on gm.userid = u.id)
left join mdl_groups as g on gm.groupid = g.id)
where co.id = 89 and co.id = g.courseid and r.id = 5 order by g.name
--------------------------------------------------------
--- dados de alunos que não estão em nenhum grupo de uma sala ---
select u.username, u.firstname, u.lastname, u.email, u.city from (((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id) left join mdl_groups_members as gm on gm.userid = u.id)
where co.id = 89 and r.id = 5 and u.id not in (select u.id from ((((((moodle.mdl_user as u join moodle.mdl_role_assignments as a on u.id = a.userid)
join moodle.mdl_role as r on a.roleid = r.id) left join moodle.mdl_context as c on c.id = a.contextid)
left join moodle.mdl_course as co on c.instanceid = co.id) left join mdl_groups_members as gm on gm.userid = u.id)
left join mdl_groups as g on gm.groupid = g.id)
where co.id = 89 and co.id = g.courseid and r.id = 5)
-------------------------------------------------------