New report from two tables with reportviewer doesnt have true result

Question

I create a new view in SQL Server 2008 from two tables that have relation on a field. I want to create a report and do grouping on that common field.

For example:

table1: student(ID,first-name,last-name,phone,address,...)
table2: courses(ID,fk_ID,Course,....)

Now I want to have report that shows all data from both tables with grouping on ID from student table, that must show courses information separated for every student. my query is:

SELECT TOP (100) PERCENT 
    dbo.tbl_student.ID,
    dbo.tbl_student.firstname, dbo.tbl_student.lastname,
    dbo.tbl_courses.Coursename, 
    dbo.tbl_Courses.CourseDate, dbo.tbl_courses.coursetype,  
FROM
    dbo.tbl_student LEFT OUTER JOIN
    dbo.tbl_courses ON dbo.tbl_student.ID = dbo.tbl_courses.fk_id 
ORDER BY 
    dbo.tbl_student.firstname DESC

But when I create a new report from this view, it shows just one record for every group. I spent 2 hours to solve the problem but I did not succeed. please help me to create report from two or more tables.

Now it shows one record duplicates for several times for every group

There is nothing wrong with your query, so if it is not returning the results you expect, probably your data isn't what you think it is. — Tab Alleman

shA.t shA.t · Accepted Answer · 2015-05-11T15:09:00

Have you tried a query like this:

SELECT s.[ID], s.[first-name], s.[last-name], s.[phone], ...
       c.[ID], c.[Course], ...
FROM student s 
     LEFT OUTER JOIN
     courses c ON s.[ID] = c.[fk_ID]

New report from two tables with reportviewer doesnt have true result

1 Answers