Query outputting more records than it should do_问答_开发者

Query outputting more records than it should do

开发者 https://www.devze.com 2023-04-13 08:46 出处：网络

I have an SQL query wtih a lot of joins. The fact is that before the query had to join the StudentClass table and Class table, it outputted the correct amount of rows which should be 4 rows. But when

I have an SQL query wtih a lot of joins. The fact is that before the query had to join the StudentClass table and Class table, it outputted the correct amount of rows which should be 4 rows. But when I include the Class and StudentClass tables, then it outputs 6 rows when it should still output 4 rows. The problem is that if a student is taking 2 modules and are in 2 classes, then it outputs 4 modules which is the 2 modules duplicated and 4 classes with the 2 classes duplicated. Why is this happening, is it something wrong with the way I set up the tables or is it the query?

Below is the PHP code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

  <head>
    <title>Exam Grade Report</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  </head>

  <body>

    <?php
      if (isset($_POST['submit'])) {

        $query = "
          SELECT * FROM Course c
            INNER JOIN CourseModule cm ON c.CourseId = cm.CourseId
            JOIN Module m ON cm.ModuleId = m.ModuleId 
            JOIN Session s ON m.ModuleId = s.ModuleId
            JOIN Grade_Report gr ON s.SessionId = gr.SessionId
            JOIN Student st ON gr.StudentId = st.StudentId
            JOIN StudentClass sc ON st.StudentId = sc.StudentId
            JOIN Class cl ON sc.ClassId = cl.ClassId 
          WHERE
            ('".mysql_real_escape_string($sessionid)."' = '' OR gr.SessionId = '".mysql_real_escape_string($sessionid)."')
          AND
            ('".mysql_real_escape_string($courseid)."' = '' OR cl.CourseId = '".mysql_real_escape_string($courseid)."')
          AND
            ('".mysql_real_escape_string($moduleid)."' = '' OR cl.ModuleId = '".mysql_real_escape_string($moduleid)."')
          AND
            ('".mysql_real_escape_string($classid)."' = '' OR sc.ClassId = '".mysql_real_escape_string($classid)."')
    开发者_如何学运维      AND
            ('".mysql_real_escape_string($teacherid)."' = '' OR s.TeacherId = '".mysql_real_escape_string($teacherid)."')
          AND
            ('".mysql_real_escape_string($studentid)."' = '' OR gr.StudentId = '".mysql_real_escape_string($studentid)."')
          AND
            ('".mysql_real_escape_string($grade)."' = '' OR gr.Grade = '".mysql_real_escape_string($grade)."')
          ORDER BY $orderfield ASC";

        $num = mysql_num_rows($result = mysql_query($query));
        mysql_close();

    ?>

  </body>
</html>

I want to show the query result and tables which I have got on a word document. How can I attach a word file by the way so that you can all open up the word document if you want to?

Thank You

Have you tried changing all the JOINs to LEFT JOINs?