CODEWITHSUNDEEP
sqlsql-server
endyourif
endyourif

Reputation: 2202

Return a list of students who attended the same class

I'm struggling with the logic behind how to accomplish the following query in MSSQL.

How can I return a list of two students and any classes they attended together? E.g.

+-----------+------------+------------+
|Student1   | Student2   | Class Name |
+-----------+------------+------------+
|John Smith | Jane Doe   | Trig       |
+-----------+------------+------------+
|John Smith | Jane Smith | Math       |
+-----------+------------+------------+

Upvotes: 1

Views: 3215

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can do this with a self-join:

select c1.student, c2.student, c1.class
from ClassStudents c1 join
     ClassStudents c2
     on c1.class = c2.class and
        c1.student < c2.student;

If two students attended multiple classes together, this will return one row per class.

If you are using ids and not names in the tables (a good idea), then you will need to join in the appropriate information from other tables. I'll leave that up to you.

EDIT:

To count the number of classes two students have had together, you would use group by:

select c1.student, c2.student, count(*) as NumClasses
from ClassStudents c1 join
     ClassStudents c2
     on c1.class = c2.class and
        c1.student < c2.student
group by c1.student, c2.student;

You can use a having clause to filter by count(*). You can join this back to the other tables to get the specific classes.

Here is how you can join this back to the original tables:

select s1.name as student1, s2.name as student2, count(*) as NumClasses
from ClassStudents c1 join
     ClassStudents c2
     on c1.class = c2.class and
        c1.studentid < c2.studentid join
     Students s1
     on c1.studentid = s1.studentid join
     Students s2
     on c2.studentid = s2.studentid
group by c1.name, c2.name
having count(*) > 1;

You could also do this as a subquery, but this is easy enough.

Upvotes: 3

Mzf
Mzf

Reputation: 5260

Creating the list for each student is easy, than you Merge the two result using WHEN MATCHED THEN

for example: let call classes that student A attend SAC1and SAC2 for the classes for student B

MERGE SAC1 AS target
   USING SAC2 AS source 
   ON (target.class = source.class)
WHEN MATCHED THEN 
    --insert into result table

if you want list of all the student that attend the same class do this 

MERGE studentClasses AS target
   USING studentClasses AS source 
   ON ((target.class = source.class) AND (target.studentId <> source.studentId))
WHEN MATCHED THEN 
    --insert into result table

Upvotes: 0

Related Questions