my data base consits of five tables
dbo.t_students lists all students, every student who take a course gets a 100 points, currently
there are there rows in dbo.t_projects
project id Student_ID project_count
1 111 150
2 4 150
3 111 150
for example student with id 111 took 12 course (point_count = 100*12 = 1200)
and project_count (300)
the first problem is when i use the following query
SELECT sum(dbo.t_points.point_count) as tcount,sum(dbo.t_projects.project_count) as pcount,max(dbo.t_students.Student_Name) as name, max(dbo.t_students.Father_name) as father, max(dbo.t_students.Student_ID) as Student_ID
FROM dbo.t_students, dbo.t_projects, dbo.t_student_course, dbo.t_groups, dbo.t_points
WHERE dbo.t_student_course.StudentId = dbo.t_students.Student_ID
AND dbo.t_groups.id = dbo.t_student_course.id
AND dbo.t_points.point_id = dbo.t_groups.point_id
AND dbo.t_projects.Student_ID in (select dbo.t_projects.Student_ID from dbo.t_projects where dbo.t_projects.Student_ID = dbo.t_students.Student_ID )
GROUP BY dbo.t_students.Student_ID,dbo.t_projects.project_c ount
i get the following result
tcount pcount name father Student_ID
1200 1800 student1 aaa 111 pcount is wrong it 300 only not 1800 (12 course *150 =1800 )
100 150 student2 bbb 4
the second problem, i want to list all students in t_students with thier tcount and pcount not only students who took project like now it displys olny two rows
attached text file.
thanks for your help