To demonstrate what is going on with all those cross apply statements consider this simple example:
create table #t1 (regno int, result int, semester int)
insert into #t1 values
(1, 10, 6),(1, 15, 7),(1, 20, 8),(1, 5, 5),
(2, 4, 5),(2, 6, 6),(2, 8, 7),(2, 10, 8);
If I add in just one cross apply
select * from
(
(select regno, result, semester
from #t1
where semester = 5) a
cross apply
(select regno, result,semester
from #t1
where semester = 6) b
)
I get the following returned
regno result semester regno result semester
1 5 5 1 10 6
2 4 5 1 10 6
1 5 5 2 6 6
2 4 5 2 6 6
I've ended up with a row for regno=1, semester=5 cross applied to regno-1, semester=6 and another for regno=1, semester=5 cross applied to
regno=2, semester=6
You can see an article on Cross Apply here -
SQL Server CROSS APPLY and OUTER APPLY[
^]
I suspect what you are trying to do is to get results like this:
Regno 5 6 7
1 5 10 15
2 4 6 8
where the 5, 6 and 7 across the top are the semesters (it's not good practice to store these as 5th, 6th, 7th).
To do that you need to use a PIVOT operator, see
SQL Server PIVOT Operator Explained Clearly By Practical Examples[
^]. In my simple example that would look like this
select * from
(
select Regno, semester, result as totalSub
FROM #t1
WHERE SEMESTER in (5,6,7,8)
) src
PIVOT
(
sum(totalSub) for Semester in ([5],[6],[7])
) pvt
And for your tables would look something like this (untested)
select * from
(
select DISTINCT s.Regno, r.SEMESTER, sj.Dsubject as Sub8, r.total as totalSub
FROM Student S
INNER JOIN RESULT r ON s.Regno = dbo.RESULT.REGNO
INNER JOIN subjects sj ON r.SUBJECTID = sj.ID
INNER JOIN Sessions ss ON s.Session = ss.ID
WHERE (r.SEMESTER in ('5th','6th','7th','8th'))
)w
PIVOT
(
SUM(totalSub) for SEMESTER IN (5th,6th,7th,8th)
) p