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

You use a left join from the table of students to your attendance table. — Sean Lange, Oct 09 '18 at 16:41
@SeanLange But teachers can only view student attendance after selecting date which is a column in table `Attendance`. That means I can't show **Student A** attendance record to teachers too — Conard, Oct 09 '18 at 16:45
use the parameter in the join clause, not your where clause. `left join attendance on <...> and @AttendanceDate = someColumn` — S3S, Oct 09 '18 at 16:47
Huh??? Are you saying they want to view a given students attendance on a certain day and they can't select the date because the student doesn't have an entry for that day? Sounds like you need to rethink some stuff. — Sean Lange, Oct 09 '18 at 16:48
Just put the date check in the "on" clause of the left outer join (not the "where") clause. Otherwise you're effectively negating the "left" qualifier of the "left join" — Xedni, Oct 09 '18 at 16:53
@SeanLange System should be able to show all student attendance records, but for me I can't show Student A attendance record because there is no record being stored in table `Attendance` for Student A. When teachers wanted to view attendance record, they need to choose the date (a column in table `Attendance`) . So I can't really show the attendance records for those who are absent — Conard, Oct 09 '18 at 16:57
@Xedni Ya, I put my date check in the WHERE clause. Do you have any example for putting date check in ON CLAUSE? — Conard, Oct 09 '18 at 16:58
Don't use the Attendance table to select the date. You should have a calendar table with all potential dates. — Sean Lange, Oct 09 '18 at 17:00

score 0 · Answer 1 · answered Oct 09 '18 at 17:01

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.

score 0 · Accepted Answer · answered Oct 09 '18 at 17:30

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

Auto-generated row on Microsoft SQL Server

2 Answers2