1

Table Defination

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'.

1

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.

| improve this answer | |
3

You can approach this problem using conditional aggregation based filtering. Firstly, get all the students who are either enrolled IN(..) course no (1, 3).

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: (course_number, student_name)

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.