I am currently working on fingerprint-based student attendance system. I am using Arduino Uno and fingerprint sensor to get student attendance record and store in SQL Server. I have a table called Attendance in my database which is storing student attendance records.But I have a problem with that.
Lets say Student A is absent today, the attendance record will not be stored in table Attendance for Student A. Meaning that I can only show students who are present to school today, the attendance record for Student A cannot be shown to the teachers.
Teachers can only view student attendance record after selecting the date, Student A attendance record cannot be seen by teachers cause there is no records for Student A in table Attendance
So How can I solve this? Any suggestion or reference? Your help is appreciated. Thank you
Assuming you're passing in the date as a parameter, you can just left outer join to the attendance table, and put the date check in the on clause (as opposed to the where clause. Putting it in the where clause effectively turns a left outer join into an inner join, due to the order in which the query is parsed and executed.
select *
from [student] s
left outer join [attendance] a
on s.[id] = a.[id]
and a.[attendanceRecordDate] = @date