Auto-generated row on Microsoft SQL Server

Question

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

Answer 1

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

Answer 2

SELECT * 
FROM STUDENT
LEFT JOIN ATTENDANCEMARK
ON STUDENT.ID = ATTENDANCEMARK.STUDENTID
WHERE ATTENDANCEMARK.DATE = @DATE OR ATTENDANCEMARK.DATE IS NULL

Explanation: you are looking to list all students with today's attendance mark, but want to list all students even if they have no attendance mark.