I need some help with a tricky little problem that I'm spending way too much time on - I'm all JOINED out.
I'd like a simple SQL statement that will select all the students in a course who are not in a group.
users is the table is users
user_students is the table that lists the students for a course
groups is the table that shows all the groups in a course
groups_members is the table that shows all the members of each group
I want a list of the relevant users who are a member of user_students, but not a member of any of the groups.
A free copy of Moodle 1.2 to the first person who can come up with a good solution!
This worked for me on mysql 4.0.15
select * from mdl_user_students left join mdl_groups_members using(userid) where mdl_groups_members.id is null;
normally, I prefer sub-select queries (being spoiled by postgresql that way) but mysql offers sub-selects only as of version 4.1
for reference:
http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#Rewriting_subqueries
select * from mdl_user_students left join mdl_groups_members using(userid) where mdl_groups_members.id is null;
normally, I prefer sub-select queries (being spoiled by postgresql that way) but mysql offers sub-selects only as of version 4.1
for reference:
http://www.mysql.com/documentation/mysql/bychapter/manual_SQL_Syntax.html#Rewriting_subqueries
Thanks, Bruno.
Unfortunately, we also need to check that the groups they are not a part of are in the current course. For example, it's possible the user could be part of a group in another course. The groups table does need to be involved.
Unfortunately, we also need to check that the groups they are not a part of are in the current course. For example, it's possible the user could be part of a group in another course. The groups table does need to be involved.
Hi,
I think is is possible to obtain the right answer without using the group table. The information about the course id is also included in mdl_user_students. ( normalisation
)
If you add something to Bruno query:
SELECT *
FROM mdl_user_students
LEFT JOIN mdl_groups_members
USING ( userid )
WHERE mdl_groups_members.id IS NULL
HAVING course =X ( x your course number )
work well here on test database.
I hope it work and that it may help,
Bernard
I think is is possible to obtain the right answer without using the group table. The information about the course id is also included in mdl_user_students. ( normalisation
)If you add something to Bruno query:
SELECT *
FROM mdl_user_students
LEFT JOIN mdl_groups_members
USING ( userid )
WHERE mdl_groups_members.id IS NULL
HAVING course =X ( x your course number )
work well here on test database.
I hope it work and that it may help,
Bernard
Thanks, but that isn't it.
Here is an example. You are a member of a group in "Using Moodle", but you are not a member of any groups in the "Documentation" course. I am looking to find all the people in the "Documentation" course who are not part of any groups in the Documentation course.
Both of your queries will pick up that you are both a student in "Documentation" and a member of a group on the same site, thus you will not be included in my required list of users who are not part of a Documentation group.
We need to make sure the set of groups being tested are those defined as part of the current course.
Is that clearer?
(courseid has to be in user_students ... there is no normalisation problem there because groups are optional and may also later be defined at a site level)
Here is an example. You are a member of a group in "Using Moodle", but you are not a member of any groups in the "Documentation" course. I am looking to find all the people in the "Documentation" course who are not part of any groups in the Documentation course.
Both of your queries will pick up that you are both a student in "Documentation" and a member of a group on the same site, thus you will not be included in my required list of users who are not part of a Documentation group.
We need to make sure the set of groups being tested are those defined as part of the current course.
Is that clearer?
(courseid has to be in user_students ... there is no normalisation problem there because groups are optional and may also later be defined at a site level)
Hi, his majesty
perhaps this should help
:
select us.userid
from mdl_user_students us
left join mdl_groups g
on g.courseid = us.course
left join mdl_groups_members gm
on gm.userid = us.userid and gm.groupid = g.id
where us.course = X
group by us.userid
having ifnull(max(gm.userid),-1) < 0
Please, don't ask me why this works!!
And remember that it only works in MySQL. Perhaps replacing the ifnull() function (from MySQL) to nvl() (Oracle & PostgreSQL) will be enough, but I'm not really sure 
Anyway, it's awful cannot be able to use boolean queries
:
select us.userid from mdl_user_students us
where us.course = X
minus
select gm.userid
from mdl_groups g, mdl_groups_members gm
where g.courseid = X and
gm.groupid = g.id
or subselects:
select us.userid from mdl_user_students us
where us.course = X and
not exists (select 'x' from mdl_groups g, mdl_groups_members gm
where g.courseid = us.course and
gm.userid = us.userid and
gm.groupid = g.id)
Some day, MySQL reqs. should go to 4.1, I think.
Finally, as a comment, having the courseid in mdl_groups_members could simplify things a lot !!
Hope it helps, ciao
perhaps this should help
select us.userid
from mdl_user_students us
left join mdl_groups g
on g.courseid = us.course
left join mdl_groups_members gm
on gm.userid = us.userid and gm.groupid = g.id
where us.course = X
group by us.userid
having ifnull(max(gm.userid),-1) < 0
Please, don't ask me why this works!!
Anyway, it's awful cannot be able to use boolean queries
select us.userid from mdl_user_students us
where us.course = X
minus
select gm.userid
from mdl_groups g, mdl_groups_members gm
where g.courseid = X and
gm.groupid = g.id
or subselects
select us.userid from mdl_user_students us
where us.course = X and
not exists (select 'x' from mdl_groups g, mdl_groups_members gm
where g.courseid = us.course and
gm.userid = us.userid and
gm.groupid = g.id)
Some day, MySQL reqs. should go to 4.1,
Finally, as a comment, having the courseid in mdl_groups_members could simplify things a lot !!
Hope it helps, ciao
Many thanks, Lord Eloy! 
Wow, no wonder I was having trouble putting that together! It's unfortunate about the database-dependence. Moodle 2.x can perhaps specify MySQL 4.1 as a minimum but it wouldn't be a good idea to do so now.
I'm very tempted to just add a courseid to groups_members - though that would be a bit of data normalisation problem.
However, I think what I'll do is use PHP instead ... get all the users first and then remove the ones in groups. The only place this is going to be used is on the Participants page, when the course is in Forced Separate Groups mode and the current user is not in a group - I thought it made sense that these people would only see the other people who are not in a group.
Wow, no wonder I was having trouble putting that together! It's unfortunate about the database-dependence. Moodle 2.x can perhaps specify MySQL 4.1 as a minimum but it wouldn't be a good idea to do so now.
I'm very tempted to just add a courseid to groups_members - though that would be a bit of data normalisation problem.
However, I think what I'll do is use PHP instead ... get all the users first and then remove the ones in groups. The only place this is going to be used is on the Participants page, when the course is in Forced Separate Groups mode and the current user is not in a group - I thought it made sense that these people would only see the other people who are not in a group.
Hi,
I am feeling like a court's joker
, between his Magesty and a Lord or the Lord , thinking that his =X contribution give the inspiration to the Lord
More seriously, a good lesson of SQL for me and a good recall that something half tested ( 3 courses but with only one having groups ) generally work in only half of the cases
.
Bye,
Bernard
p.s. Is there an "automatic diagramming tool" for MySql that works like MS Access?
I am feeling like a court's joker
, between his Magesty and a Lord or the Lord , thinking that his =X contribution give the inspiration to the LordMore seriously, a good lesson of SQL for me and a good recall that something half tested ( 3 courses but with only one having groups ) generally work in only half of the cases
.Bye,
Bernard
p.s. Is there an "automatic diagramming tool" for MySql that works like MS Access?
No worries, man, it can happen to any of us. Thanks for trying (and Bruno too)
Can't answer your Access question, sorry.
Can't answer your Access question, sorry.
Yes, there are: MS Access...
Use the ODBC provided by MySQL... Then attach your mysq tables to a blank db and make the connections in the tool provided by MSAccess... It won't be of any help in Moodle thought. Use it only as a way to help you walk into the moodle db.
I hope it helps,
Will
Use the ODBC provided by MySQL... Then attach your mysq tables to a blank db and make the connections in the tool provided by MSAccess... It won't be of any help in Moodle thought. Use it only as a way to help you walk into the moodle db.
I hope it helps,
Will
Hi Will,
thanks it work fine.
Bye,
Bernard
thanks it work fine.
Bye,
Bernard
Hi Bernard,
I haven't tried it yet myself but could DBDesigner at http://fabforce.net/dbdesigner4/ be something for you. I've picked it up somewhere else on these forums.
I haven't tried it yet myself but could DBDesigner at http://fabforce.net/dbdesigner4/ be something for you. I've picked it up somewhere else on these forums.
Hi Hans,
thanks for the link. Look better than Access itself!
Bye,
Bernard
thanks for the link. Look better than Access itself!
Bye,
Bernard