Filtering in Many-to-many relationship

user_4933706 0
Viewed 25

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

many-to-many mysql
2 Answers

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 |
user_51760710

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;
user_109106920
Related