This is the table definition. I want to select the students that are enrolled in courses (1 and 3).
From this dataset the expected output is 'B'.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can use the following query:
SELECT student_name FROM <table> WHERE course_number IN(1,3) GROUP BY student_name HAVING COUNT(distinct course_number) = 2;
If you have a different dataset (say course_numbers 1,2,3) you can use course_number in (1,2,3) and COUNT(distinct course_number) = 3 and so on.
You can approach this problem using conditional aggregation based filtering. Firstly, get all the students who are either enrolled
IN(..) course no
Then, you can use
HAVING clause to filter out the students having atleast two unique courses subscribed to (which would be 1 and 3 in this case):
SELECT student_name FROM your_table_name WHERE course_number IN (1,3) GROUP BY student_name HAVING COUNT(DISTINCT course_number) = 2
For good performance, you can define the following Covering Index: