CODEWITHSUNDEEP
mysqlsql
Yehia
Yehia

Reputation: 15

select ids that doesn't have a specific values

I have a table with the schema below:

+---------+--------+
|studentId | course | 
+---------+--------+
|1        | 2      | 
|1        | 3      | 
|1        | 4      | 
|1        | 5      | 
|2        | 4      | 
|2        | 5      | 
+---------+--------+

and I want to perform a query to get student Ids that don't have course 2 and 3

select * from students where course not in (2,3);

but it returns Students IDs 1 and 2 and I want it to return Student ID 2 only.

How do I do that?

Upvotes: 1

Views: 134

Answers (7)

AdamMc331
AdamMc331

Reputation: 16691

I would recommend using NOT IN and writing a subquery that pulls for each student who is taking courses 2 or 3. That is, if you are looking for students who are not taking course 2 or 3. If you are looking to exclude students who are taking BOTH courses, this will need to change a little bit. Let me know if that is the case.

Start by writing the subquery, which is easy enough:

SELECT * 
FROM students
WHERE course = 2 OR course = 3

Then, you can select from your table again using the NOT IN operator:

SELECT DISTINCT studentid
FROM students
WHERE studentid NOT IN (SELECT studentid
                        FROM students
                        WHERE course = 2 OR course = 3);

And it works!

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

SELECT DISTINCT x.studentid 
           FROM student x
           LEFT 
           JOIN 
              ( SELECT studentid 
                  FROM student 
                 WHERE course IN(2,3) 
                 GROUP 
                    BY studentid 
                HAVING COUNT(*) = 2 ) y
             ON y.studentid = x.studentid
          WHERE y.studentid IS NULL;

(of course, it's highly unlikely that a table holding students and courses would be called student. enrolment might be a better title)

Upvotes: 0

wolfgangwalther
wolfgangwalther

Reputation: 1236

This answers assumes that OP wants to filter out students that have either course 2 or course 3 or both of them set.

At first, find all students, who have course 2 or 3

SELECT DISTINCT studentId
FROM students
WHERE course IN (2,3)

Then, find all students, who are not in that list

SELECT *
FROM students
WHERE studentId NOT IN (...)

If you only want to return a list of studentIds, without their courses, replace * with DISTINCT studentId.

Put those together:

SELECT DISTINCT studentId
FROM students
WHERE studentId NOT IN (
  SELECT DISTINCT studentId
  FROM students
  WHERE course IN (2,3)
)

Upvotes: 2

winmutt
winmutt

Reputation: 405

JR's query will perform poorly MySQL < 5.6 and only performs an OR not an AND on course.

Try this:

SELECT 
  id 
FROM foo AS missingfoo 
LEFT JOIN (
           SELECT id FROM foo AS foo1 
           JOIN foo AS foo2 USING (id) 
           WHERE foo1.course=2 
           AND foo2.course=3
          ) AS z 
USING (id) WHERE z.id IS NULL GROUP BY id;

Upvotes: -1

FuzzyTree
FuzzyTree

Reputation: 32392

Another query using having to filter out students that have courses 2 or 3

select studentId
from students
group by studentId
having sum(course in (2,3)) = 0

Upvotes: 1

JanR
JanR

Reputation: 6132

You could do it like this:

select * from students where studentId not in -- exclude all students from course 2 & 3
(
    --find all the students in course 2 & 3
    select distinct studentId --could be duplicates might as well grab a distinct list.
    from students 
    where course in (2,3)
)

Upvotes: 2

Jacob Lambert
Jacob Lambert

Reputation: 7679

This should work:

select
    *
from
    students s
where
    not exists ( select
                     1
                 from
                     students ss
                 where
                     ss.studentID = s.studentID
                     and ss.course in (2,3));

Upvotes: 0

Related Questions