I have 3 tables:
years. In a fourth table called
StudentsInClass, I'm saving students classes in each year. Now I want to show a list of students with their
className in Current Year.
This is the query I use but I'm not feeling good with it:
@StartRow int, @EndRow int As SET NOCOUNT ON; begin declare @YearID int; set @YearID= (select top 1 ID from Years where IsCurrent =1 ); select * from( select top 10000 S.*,T2.Title as ClassName,Row_Number() over (order by S.ID) as ResultSetRowNumber from Students S left outer join ( select SC.StudentCode,T1.Title from (select * from StudentsInClass where YearID=@YearID) SC left outer join Classes T1 on SC.ClassID = T1.ID ) T2 on S.Code= T2.StudentCode order by S.Family asc ) as PagedResults where ResultSetRowNumber > @StartRow and ResultSetRowNumber <= @EndRow; end