2
\$\begingroup\$

I have 3 tables: students and classes and 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
\$\endgroup\$
3
\$\begingroup\$

Not entirely sure I got your schema right, but something like this should be possible. (omitted a bit of ordering, and I assume you only have one current year)

select top 10000
    s.*,
    c.Title classname,
    row_number() over (order by s.id) rownumber,
from
    students s
    inner join years
        on years.iscurrent = 1
    inner join studentsinclass sc
        on s.code = sc.studentcode and sc.yearid = years.id
    inner join class c               
        on c.id = sc.classid
where
    row_number() over (order by s.id) >= @StartRow
    and
    row_number() over (order by s.id) <= @EndRow

You could possibly swap on years.iscurrent = 1 with on years.id = year(getdate()).

If you can, you should generally try to avoid too many subqueries. At least attempt to indent them properly.

You could also benefit from having a look at common table expressions. These can help you group up the subqueries in predefined results for your query. Makes it a lot easier to read. http://msdn.microsoft.com/en-us/library/ms190766.aspx

\$\endgroup\$
  • \$\begingroup\$ +1 Joins rule; Something that helped me as a beginner was looking at the table schemas printed out on paper, this gets almost mechanical - just have to get from table A to table B traversing by keys, doing joins along the way. \$\endgroup\$ – Leonid May 20 '12 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.