Page 1 of 1

How to get the record?

Posted: Wed Apr 03, 2013 4:15 am
by Tassadduq
Hi everyone, i have two tables one for students records and one for daily attendance record. Every student has his own student code to use for daily attendance. In morning ever one comes and mark his attendance using his code. Attendance table stores the current date and student id. Now the issue is very simple. i want to get the students list those were absent today means not in attendance table.

Here are the tables structure.

Students
.sid
.name
.scode

Attendance
.id
.stdid
.date


i want to get the students list that are not in attendance table on given date.

Re: How to get the record?

Posted: Wed Apr 03, 2013 12:32 pm
by requinix
SELECT from the attendance table then RIGHT JOIN the students and filter WHERE the student is NULL (ie, no row joined) and the attendance date is today.

Re: How to get the record?

Posted: Thu Apr 04, 2013 5:23 am
by Tassadduq
Can you please write the query? how to??

i made it like this

Code: Select all

SELECT attendance.*, students.name FROM attendance RIGHT JOIN students ON (attendance.std_id = students.id) WHERE att_date = '2013-04-04' AND std_id = NULL;
but it did not work... :(
Any help please....

Re: How to get the record?

Posted: Thu Apr 04, 2013 1:25 pm
by requinix
I've thoroughly confused myself now... I could swear you've edited your post... but I am confident I misspoke: the attendance date needs to be in the JOIN condition.

Code: Select all

SELECT attendance.*, students.name FROM attendance RIGHT JOIN students ON (attendance.std_id = students.id AND attendance.att_date = '2013-04-04') WHERE attendance.std_id IS NULL

Re: How to get the record?

Posted: Thu Apr 04, 2013 1:38 pm
by Tassadduq
Thanks alot. it works superbbbbb..... :)