查询输出的行数多于应该执行的行数

Question

Below is my query:

$query = "
          SELECT gr.SessionId, t.TeacherUsername, s.ModuleId, m.ModuleName, 
      st.CourseId, st.Year, st.StudentUsername, gr.Mark, gr.Grade
      FROM Teacher t
          INNER JOIN Session s ON t.TeacherId = s.TeacherId
          JOIN Grade_Report gr ON s.SessionId = gr.SessionId
          JOIN Student st ON gr.StudentId = st.StudentId
          JOIN Course c ON st.CourseId = c.CourseId
          JOIN Course_Module cm ON c.CourseId = cm.CourseId
          JOIN Module m ON cm.ModuleId = m.ModuleId
          WHERE
            ('".mysql_real_escape_string($sessionid)."' = '' OR gr.SessionId = '".mysql_real_escape_string($sessionid)."')
          ";

Below is what is query is outputting:

Session ID  TeacherUsername Module Number   ModuleName  Course ID   Year    Student Username    Mark    Grade
AAA            m.prigmore   CHT2520        Web Program  INFO102      1      u1231231            69       B
AAA            m.prigmore   CHT2520        Database     INFO102      1      u1231231            69       B
AAA            m.prigmore   CHT2520        Web Program  INFO102      2      u0833421            71       A
AAA            m.prigmore   CHT2520        Database     INFO102      2      u0833421            71       A

If you look carefully what is happening is that in the Course_Module table which I have used to JOIN the Course table and Module table, there are 2 modules for the course 'INFO102'. Because of this it is showing 4 rows as it is trying to show two Modules Names (which comes from module table) for a Session for each student. This is incorrect as it should only link to the module which belongs to the session which should be 'Web Program', not 'Database' (This is because ModuleNumber CHT2520 = 'Web Program' in Module Table).

Below is what it should output:

Session ID  TeacherUsername Module Number   ModuleName  Course ID   Year    Student Username    Mark    Grade
    AAA            m.prigmore   CHT2520        Web Program  INFO102      1      u1231231            69       B
    AAA            m.prigmore   CHT2520        Web Program  INFO102      2      u0833421            71       A

So how can I get rid of the rows which says 'Database' under 'ModuleName' and that Module Number only matches with the correct Module Name?

Thank You

Answer 1

The problem is that you are getting ModuleNumber from the Session table and ModuleName from the Module table. Your query says, "for this session, get all grade reports, and for those grade reports, get all students, and for all students, get all courses, and for all courses, get all course-module links, and for all course-module links, get all modules."

That's why you see all modules listed for that combination of grade report, student, course and module. You could add "AND s.ModuleId = m.ModuleId" and that should fix it. I presume you have ModuleId in the session table, if you also have ModuleNumber. And if so, you don't need ModuleNumber in your Session table.