CODEWITHSUNDEEP
sqlsql-server
D. Jhon
D. Jhon

Reputation: 21

Query to find students who took many courses together

How to write a query to find all students who took many courses together.

Tables schema:

For example: I want to see students who have taken more than three courses together.

I want result like this:

Course.name, Student.fname, student.lname
CSC 201,        Jhon ,         Khal
CSC 201,        Mike,          Alan
CSC 201,        Peter,         Wick
IS 220,         Jhon ,         Khal
IS 220,         Mike,          Alan
IS 220,         Peter,         Wick
.               Jhon ,         Khal
.               Mike,          Alan
.               Peter,         Wick
.
.
.
.

This is what I came up with:

select Course.name, Student.fname, student.lname  from enrollment e
join course c on c.course.Id = e.course.Id
join student s on s.id = e.student_ID

Upvotes: 1

Views: 1746

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

How to write a query to find all students who took many courses together.

I would use aggregation and having. For three arbitrary courses:

select s.id, s.fname, s.lname 
from student s join
     enrollment e
     on s.id = e.student_ID 
group by s.id, s.fname, s.lname 
having count(*) >= 3;

Notice that the courses table is not needed. enrollment has sufficient information. This is not returning information about the courses -- because your question is only asking for the students who meet the criterion.

If you want to see the courses, then string_agg() is the simplest solution to put the values on one row:

select s.id, s.fname, s.lname,
       string_agg(c.name, ', ') as courses
from student s join
     enrollment e
     on s.id = e.student_ID join
     course c
     on c.course.Id = e.course.Id
group by s.id, s.fname, s.lname 
having count(*) >= 3;

Do note that string_agg() is a recent addition to SQL Server, so it may not be available in your version.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Try this below script-

SELECT C.Name course_name, 
A.fname,
A.lname
FROM Student A
INNER JOIN Enrollment B ON A.ID = B.Student_ID
INNER JOIN Course C ON B.Course_id = C.Course_id
WHERE A.ID IN
( 
    SELECT Student_ID  
    FROM Enrollment
    GROUP BY Student_ID  
    HAVING COUNT(*) >= 3 
)

Upvotes: 0

Related Questions