CODEWITHSUNDEEP
phpmysqlsql
minovsky
minovsky

Reputation: 857

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);

Upvotes: 5

Views: 4570

Answers (4)

Dennis S.
Dennis S.

Reputation: 2121

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.

Upvotes: 3

Brian Glaz
Brian Glaz

Reputation: 15676

You could try something like this:

SELECT
    *
FROM
    students
WHERE
    id NOT IN
        ((SELECT 
            student_id
        FROM
            students_groups
        WHERE
            group_id = 1
        ))

Upvotes: 2

Bort
Bort

Reputation: 7618

Using a NOT IN should work fine:

SELECT * FROM Students
WHERE Id NOT IN (
    SELECT Student_Id FROM Students_Groups
    WHERE Group_Id = 1)

Upvotes: 11

lc.
lc.

Reputation: 116458

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)

or...

select * 
from students 
where id not in (select student_id
                  from students_groups 
                  where group_id = 1)

or...

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

Upvotes: 2

Related Questions