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

closed as off-topic by Laurenz Albe, Michael Green, mustaccio, LowlyDBA, Marco Sep 12 at 8:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael Green, mustaccio, LowlyDBA, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

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.

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)

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