Filtering in Many-to-many relationship

Question

I am taking a popular example here for convenience: Let's say we have a list of students and a list of courses. There is a many to many relationship between them. I can use an intermediate table to define relationship between the 2.

What I need help with is this: I want to provide a course name as input, and figure out the students who are studying the 'input' course are studying what other courses.

Example:

Student	Course
S1	C1
S1	C2
S2	C1
S3	C2
S3	C3
S4	C2
S5	C6
S5	C2

Input: C2 So S1, S3, S4, S5 are studying C2. I want to find what other courses are they studying. Output: C1, C3, C6

I'm looking for concepts or code to implement something like this in mySQL

Answer 1

You can try this:

SELECT Course
FROM mytable
WHERE Student IN (SELECT Student FROM mytable WHERE Course='C2')
GROUP BY Course;

Or if you want to exclude the input:

SELECT Course
FROM mytable
WHERE Student IN (SELECT Student FROM mytable WHERE Course='C2')
AND Course != 'C2'
GROUP BY Course;

Or multiple inputs:

SELECT Course
FROM mytable
WHERE Student IN (SELECT Student FROM mytable WHERE Course IN ('C2','C3'))
#AND Course NOT IN ('C2','C3')
GROUP BY Course;

Answer 2

You can try to do self-join

Query 1:

SELECT DISTINCT t2.Course 
FROM (
    SELECT Student 
    FROM T t1  
    WHERE Course = 'C2'
) t1 
INNER JOIN T t2 ON t1.Student = t2.Student 
WHERE t2.Course <> 'C2'

Results:

| Course |
|--------|
|     C1 |
|     C3 |
|     C6 |