Question 216956 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Finding the exclusive records in a table

I am a beginner at SQL. I have a single table called enrolment where I have users (user_id) who can have either single or multiple courses which are represented using course_id.

I want to find the users who are only enrolled into the course 10, have no due fees (due_fee) and nothing else.
This is my attempt at this problem but it is not working:

SELECT user_id FROM student_enrollment WHERE course_id = 10 and due_fee = 0.00

This query is selecting the users who have a course_id of 10 but how do I filter this so that the users who have both 10 and other course_id’s are removed? Then the end result would only be the users with course_id 10.

Tags:

3

Answers


  1. 0

    You could phrase this using exists logic:

    SELECT user_id
FROM student_enrollment se1
WHERE course_id = 10 AND
      NOT EXISTS (
          SELECT 1
          FROM student_enrollment se2
          WHERE se2.user_id = se1.user_id AND
                se2.course_id <> 10
      );

    Another approach, using aggregation:

    SELECT user_id
FROM student_enrollment
GROUP BY user_id
HAVING SUM(course_id <> 10) = 0;
    Login or Signup to reply.
  2. 0

    A simple way is to use EXCEPT: Find all users that take class #10 except those who also take another class.

    SELECT user_id FROM student_enrollment WHERE course_id = 10
EXCEPT
SELECT user_id FROM student_enrollment WHERE course_id <> 10

    https://dev.mysql.com/doc/refman/8.0/en/except.html

    Login or Signup to reply.
  3. 0

    Using conditional aggregation and filter:

    SELECT user_id
FROM
(
SELECT user_id, 
       count(case when course_id = 10 then 1 else 0 end) cnt_10,
       count(case when course_id <> 10 then 1 else 0 end) cnt_other
FROM student_enrollment s
GROUP BY user_id
) s
WHERE cnt_10>0 and cnt_other=0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search