I am trying to get information for each student in a database. I know that there are exactly 4 students and that between all students, there are 6 enrollments (ie. some students are enrolled in multiple courses). Therefore, the proper output would have 6 rows, all containing the necessary student info. There would be duplicate students in the returned query. I am able to join the students and the enrollments just fine and end up with the 6 total enrollments. However, once I join in the other tables to get data about the courses that the students are enrolled in, I end up getting more and more rows. Depending on how I format my query, I get between 7-11 rows. All that I want is the 6 rows that correspond to the enrollments and nothing more. Why does that happen like this and how do I fix it?
I have tried different kinds of joins, unions, intersections, and have been working at the question for well over an hour. This is what I have currently:
Select s.sid, e.term, c.cno, e.secno, ca.ctitle
from Students as s
join Enrolls as e
on s.sid = e.sid
join Courses as c
on e.secno = c.secno
join Catalogue as ca
on ca.cno = c.cno
>Solution :
It looks like the Courses and Enrollment tables have what we call ‘a composite key’. I bet you must join the c and e tables with both term and secno columns.
Your query mus be like this:
SELECT s.sid, e.term, c.cno, e.secno, ca.ctitle
FROM Students AS s
JOIN Enrolls AS e ON s.sid = e.sid
JOIN Courses AS c ON e.secno = c.secno AND e.term = c.term
JOIN Catalogue AS ca ON ca.cno = c.cno
When you have a composite key and uses only one of the columns to join, you will get unwanted rows from the foreign table, making a Cartesian product result