dowe98261 2016-02-08 08:45
浏览 50

MySQL - JOIN操作多个表

I am creating an Online Attendance System, and my problem is around a query below not bringing back the correct data.

I am looking to bring back a Student ID, Class ID and the Attendance Status E.G Present Late or Absent of that class.

If attendance has been taken, then the query run's fine. However, if no attendance has been taken the query returns an empty set.

I am looking to still return all the data but have 'null' or anything in the 'Attendance Status' column, If attendance hasn't been taken.

This is the query: MySQL Query

And it bring back these results if attendance has been taken for the class:

MySQL Results

However, If attendance hasn't been taken, then it will just return an empty set. What I would like is for in the attendance column it print 'null' or anything to make sure some data still comes back.

At minimum I still need the Class and Student ID. I think it may need a JOIN, I just can't get my head around it.

  • 写回答

1条回答 默认 最新

  • dongnuan7956 2016-02-08 08:57

    You should use the proper syntax of JOINS! that can cause a lot of problems and it is harder to understand.

    What you need is a LEFT JOIN which can be used using(+) after the condition of the table you want to left join to - DON'T DO IT.

    Your query should be:

     SELECT class.class_id as 'Class_ID',
            student.Student_ID as 'Student_ID',
            case when attendence.attendence_status is null then 'Not Present' else attendence.attendence_status end as 'attendence_status'
    FROM Student
    INNER JOIN Student_Has_Class ON(student_has_class.student_id = student.student_id)
    INNER JOIN class ON(class.class_id = student_has_class.class_id)
    INNER JOIN Subject ON(class.subject_id = subject.subject_id)
    LEFT JOIN Attendence on(attendence.class_id = class.class_ID
                            AND attendence.student_id = student.student_id)
    WHERE NOW() between class.class_start_timestamp and class.class_end_timestamp
    AND Student.student_id like '1'
    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥15 快手联盟怎么快速的跑出建立模型
  • ¥15 关于腾讯云服务器中,PHP页面无法打开的问题
  • ¥60 如何把照片修复成原始拍摄图
  • ¥80 Exited too quickly (process log may have details)
  • ¥15 爬知乎登录之后内容加载不出来
  • ¥15 怎么用protues测量通频带
  • ¥15 zepelin使用sparkInterpreter 异常
  • ¥15 paho mqtt 接收不到消息
  • ¥15 函数r关于两个分量y,z方向上的图像,分开画r随y的图像,r随z的图像
  • ¥15 如何用Matlab求Pearcey函数的数值积分?