Question

MySQL

I have the following tables:

Students(sid,first_name,last_name,birthday,major,zipcode)

Students_Courses(sid,cid,year,semester,grade)

Courses(cid,name,credits)

use Students_Courses table to find the student sid and course cid for whom took the same course more than one time.

can you help me with my query

select first_name,last_name,name

from Students s, Courses c

where s.sid,c.cid,ct in

( select sid,cid,count(*) from Students_Courses group by sid,cid having count(*) > 1);

Answer 1

Solution

Below query results in sid,cid for all the students who have taken the particular course more than once.

Select A.sid , A.cid

from Student_Courses as A, Student_Courses as B

where A.sid = B.sid AND  A.cid = B.cid AND (A.year != B.year OR A.semester != B.semester)

To get the student first_name,last_name and course name for the students who have taken a course more than once following query can be used:

Select S.first_name, S.last_name , C.name

from Students AS S , Courses AS C , Student_Courses as SC

where S.sid = SC.sid AND C.cid = SC.cid AND S.sid,C.cid IN

(

Select A.sid , A.cid

from Student_Courses as A, Student_Courses as B

where A.sid = B.sid AND  A.cid = B.cid AND (A.year != B.year OR A.semester != B.semester)

)