使用多个表上的多个查询从多个表中获取数据

I am creating attendance management system for a school in php and mysql. I have 3 tables in database these are:

我正在为php和mysql的学校创建考勤管理系统。我在数据库中有3个表,它们是:

1. Students
id 
student_name
class

2. subjects
id
subjects


3. attendances
id
subject_id
student_id
attend (boolean)
today_date

I am only storing students who are attending classes in attendance table.

我只存储在考勤表上上课的学生。

I want grab the list of all students (which are in or not in attendance table) who are attend or not for a particular class, date, and subject.

我想抓住所有参加或不参加特定班级,日期和科目的学生名单(参加或不参加考勤表)。

Like;

Attendance for Class - X(Physics) on 18-08-2017

    ______________________________
    | Student Name  | Attendance |
    ------------------------------
    | Foo           |     0      |
    | Bar           |     1      |
    | Baz           |     0      |
    | Qux           |     1      |
    ------------------------------

I was trying queries

我在尝试查询

SELECT s.id,s.student_name, a.attend as attend 
FROM attendances as a 
INNER JOIN students as s on s.id = a.student_id 
WHERE s.class = 'X'
AND a.subject_id = 3 
AND a.date = '2017-08-18' 

But I am not getting the result I want. I am sure there will be some better and simple way to achieve this which I am missing there, because I haven't use SQL in this way before.

但我没有得到我想要的结果。我相信会有一些更好,更简单的方法来实现这一点,因为我之前没有以这种方式使用SQL。

2 个解决方案

#1

Below query might help you to achieve desired result:

以下查询可能有助于您获得所需的结果:

SELECT s.id,s.student_name, IFNULL(a.attend, 0) as attend FROM students as s LEFT JOIN attendances as a on s.id = a.student_id WHERE s.class = 'X' AND a.subject_id = 3 AND a.date = '2017-08-18' 

#2

SELECT s.id,s.student_name, a.attend as attend FROM attendances as a 
RIGHT JOIN students as s on s.id = a.student_id AND a.subject_Id=3 
AND a.date='2017-08-18' WHERE s.class = 'X'