SQL Join Retrives more rows than expected

Question

Suppose I have the following tables

Students (StudentId int Pk, StudentName nvarchar)
Lectures (LectureId Pk, StartDate, EndDate)
Enrollment (StudentID, LectureID)

When I execute the following query:

select StudentID From Students

I get 8 rows.. And when i execute:

select S.StudentID From Students S join Enrollment En On S.StudentID = En.StudentID

I get 11 Rows

Why is that, and how to use join without retrieving extra rows ? This could be a basic SQL question.. Excuse my ignorance.. I'm just a beginner

Answer 1

Your join is fine.

This means that there is more than one Enrollment per Student - e.g. Students have enrolled in more than one lecture / series of lectures

Assuming that a student can only register for one lecture at a time, then you will also need to join to lecture and use the date fields

Also, if a student is not currently enrolled in anything, you will need to consider a left outer join.

So your query might then look like

SELECT S.StudentID FROM Students S 
LEFT OUTER JOIN Enrollment En On S.StudentID = En.StudentID
INNER JOIN Lectures l ON en.LectureId = l.Lecture ID
WHERE getdate() BETWEEN l.StartDate and l.EndDate

But you would need to have rules in place to ensure that the student cannot concurrently register for more than one lecture (if that is indeed what you expected)

HTH

Answer 2

Use a LEFT JOIN

EDIT: nevermind, it is as said below/above/whatever. You must have students with multiple lectures/enrollments. You will need to group by student to get 8 rows again.

select S.StudentID From Students S join Enrollment En On S.StudentID = En.StudentID group by S.StudentID