sql server - SQL Join Retrieves more rows than expected -

suppose have following tables

students (studentid int pk, studentname nvarchar) lectures (lectureid pk, startdate, enddate) enrollment (studentid, lectureid) 

when execute following query:

select studentid students 

i 8 rows.. , when execute:

select s.studentid students s join enrollment en on s.studentid = en.studentid 

i 11 rows

why that, , how use join without retrieving rows?

your join fine.

this means there more 1 enrollment per student - e.g. students have enrolled in more 1 lecture / series of lectures

assuming student can register 1 lecture @ time, need join lecture , use date fields

also, if student not enrolled in anything, need consider left outer join.

so query might like

select s.studentid students s  left outer join enrollment en on s.studentid = en.studentid inner join lectures l on en.lectureid = l.lecture id getdate() between l.startdate , l.enddate 

but need have rules in place ensure student cannot concurrently register more 1 lecture (if indeed expected)

hth