mysql select rows not in many-to-many relation
I have a data structure where students and groups have many-to-many relationship. I have three tables
students: id, name
groups: id, name
students_groups: student_id, group_id
How do I select only students who are not in a specific group (e.g. group.id = 1)?
I did some searching and tried using sub query but only get an empty set...
select * from students where not exists (select students.* from students left join students_groups on students_groups.student_id = student.id where students_groups.group_id = 1);
how should I query? thx much in advance!
EDIT OK, it seems the following two finally works... can anyone EXPLAIN to me why I don't need to join table for it to work???
select * from students where not exists (select * from students_groups where students_groups.student_id = student.id and student_groups.group_id = 1); select * from students where id not in (select student_id from students_groups where group_id = 1);
Using a NOT IN should work fine:
SELECT * FROM Students WHERE Id NOT IN ( SELECT Student_Id FROM Students_Groups WHERE Group_Id = 1)
Untested, but one of the following ought to work. You'll have to do some explaining and see which one is best.
select * from students where not exists (select * from students_groups where students_groups.student_id = student.id and students_groups.group_id = 1)
select * from students where id not in (select student_id from students_groups where group_id = 1)
select students.id, students.name from students left outer join students_groups on students.id = students_groups.student_id and students_groups.group_id = 1 where students_groups.student_id is null group by students.id, students.name
You could try something like this:
SELECT * FROM students WHERE id NOT IN ((SELECT student_id FROM students_groups WHERE group_id = 1 ))
The edited question asks for an explanation.
Think of SQL queries as Venn Diagrams in text. Each clause either defines a circle of content, or tells you which part of the full overlapping circles diagram you're interested in.
select * from students where id not in (select student_id from students_groups where group_id = 1);
One circle is the students table. One circle is the student_groups table where group_id = 1. The circles overlap where students.id equals student_groups.student_id. You want the part of the students table that is not in the overlap area.
You don't need to join the tables because your result set contains data only from the students table. You are using the other table to limit that result set, not provide data to your results.