mysql select rows not in many-to-many relation
mysql, php, sql
Solution
Using a `NOT IN` should work fine:
SELECT * FROM Students
WHERE Id NOT IN (
SELECT Student_Id FROM Students_Groups
WHERE Group_Id = 1)
Problem
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); ```