Alao Alao - 6 months ago 16
SQL Question

Sql Joins on multiple table returning product of two columns

I am trying to generate a report on a sql server database in asp.net and I am getting the results of some columns as a product of two columns. Here is the code

comm.CommandText = "SELECT Count(Courses.CourseID) AS CourseCount, Count(Students.StudentID) AS StudentCount, Schools.Name, Schools.StartDate, Schools.SchoolFees " +
"FROM Schools" +
"LEFT JOIN Courses ON (Schools.SchoolID = Courses.SchoolID)" +
"LEFT JOIN Students ON (Schools.SchoolID = Students.SchoolID) " +
"WHERE Schools.Active = 1 " +
"GROUP BY Schools.Name, Schools.StartDate, Schools.SchoolFees";


When I run the code, the result displays, but the columns for "CourseCount" and "StudentCount" display a value that is a product of each individual column. "CourseCount" is normally 288 and "StudentCount" is 38 but when I run the code, both "CourseCount" and "StudentCount" display 10944 which is 38 x 288.

Anyway I can make them display the correct values?

Answer

Changing your code from using a count of all rows, to a count of distinct values only, should return the results you expect

comm.CommandText = "SELECT Count(DISTINCT Courses.CourseID) AS CourseCount, Count(DISTINCT Students.StudentID) AS StudentCount, Schools.Name, Schools.StartDate, Schools.SchoolFees " +
            "FROM Schools" +
            "LEFT JOIN Courses ON (Schools.SchoolID = Courses.SchoolID)" +
            "LEFT JOIN Students ON (Schools.SchoolID = Students.SchoolID) " +
            "WHERE Schools.Active = 1 " +
            "GROUP BY Schools.Name, Schools.StartDate, Schools.SchoolFees";

The results being returned are technically correct, if all schools have courses, and all courses have students

Comments