Forum Moderators: open
i want to query an attendance list where the table is like this
attendance table:
id..... metric_no..........enter_time........... class
1.........B0001 ......2006-12-24 08:01:00.........BK1
2.........B0002 ......2006-12-24 08:02:00.........BK1
3.........B0003 ......2006-12-24 08:03:00.........BK1
4.........B0001 ......2006-12-25 08:01:00.........BK2
5.........B0002 ......2006-12-25 08:02:00.........BK2
6.........B0003 ......2006-12-25 08:03:00.........BK2
where there is a subject table that contain information about which student take what subject.
For example, subject BENT3333 contain 4 Students that take this class.
The subject table is like this:
subject table:
id......subject_code.....metric_no.....
1.........BENT3333 .........B0001......
2.........BENT3333 .........B0002......
3.........BENT3333 .........B0003......
4.........BENT3333 .........B0004......
5.........BENM4444 .........B0005......
6.........BENM4444 .........B0006......
7.........BENM4444 .........B0007......
8.........BENM4444 .........B0008......
I now have archived this output below: (exactly what i want)
Attend ......Class:BENT3333....DayTime:2006-12-24 08:01:00
1.B0001
2.B0002
3.B0003
Unattended
1.B0004
By using this SQL query:-
SELECT student.metric_no, subject.subject_code
FROM ((student LEFT JOIN subject ON subject.metric_no=student.metric_no) LEFT JOIN attendance ON attendance.metric_no=subject.metric_no)
WHERE subject.subject_code='".$_GET["subject"]."' AND (attendance.metric_no IS NULL)
BUT....
BUT if i query for the next day, all the B001,B0002,and B0003 didn't appear in UNATTENDED list, instead only B0004 is appear. The output is like this:-
Attend ......Class:BENT3333....DayTime:2006-12-25 08:01:00 //NEW DAY
1.
2.
3.
Unattended
1.B0004
Where are the 3 unattended person missing? Someone help me...
Hurm...can this be done?
BUT if i query for the next day
Can you post that query. I don't see a date range in what you have posted so far. My guess is that you are trying to get 1 day and not a grouped date range?
JAG
actually, what i want to do is like this..
the attendance table will receive an input from an RFID card.
Then, the RFID will take the id of the student, send it to attendance table.The output will be like this:
id..... metric_no..........enter_time........... class
1.........B0001 ......2006-12-24 08:01:00.........BK1
2.........B0002 ......2006-12-24 08:02:00.........BK1
3.........B0003 ......2006-12-24 08:03:00.........BK1
4.........B0001 ......2006-12-25 08:01:00.........BK2
5.........B0002 ......2006-12-25 08:02:00.........BK2
6.........B0003 ......2006-12-25 08:03:00.........BK2
So, in this table there will be have multiple metric_no from other day.
I then, want to select for example from "2006-12-24 08:00:00 till 2006-12-24 09:00:00"
i should get:
id..... metric_no..........enter_time........... class
1.........B0001 ......2006-12-24 08:01:00.........BK1
2.........B0002 ......2006-12-24 08:02:00.........BK1
3.........B0003 ......2006-12-24 08:03:00.........BK1
Then, this output will compare the attend and unattended student from table "subject"
subject table:
id......subject_code.....metric_no.....
1.........BENT3333 .........B0001......
2.........BENT3333 .........B0002......
3.........BENT3333 .........B0003......
4.........BENT3333 .........B0004......
5.........BENM4444 .........B0005......
6.........BENM4444 .........B0006......
7.........BENM4444 .........B0007......
8.........BENM4444 .........B0008......
From the output above(the attendance from "2006-12-24 08:00:00 till 2006-12-24 09:00:00") there should 1 missing right.That is B0004
I want to show this person in the same page like this:
Subject BENT3333
Attend List:
B0001
B0002
B0003
Unattended List:
B0004
Did you get it?
But, when i query for the next day,"2006-12-25 08:00:00 till 2006-12-25 09:00:00"
Those student still doesn't "key-in" their attendance right?
so, the output should be:
Subject BENT3333
Attend List:
Unattended List:
B0001
B0002
B0003
B0004
Can this be done?
SQL query for the unattended list:
SELECT student.name, student.metric_no, subject.subject_code, subject.subject_name, attendance.enter_time, attendance.class
FROM ((student LEFT JOIN subject ON subject.metric_no=student.metric_no) LEFT JOIN attendance ON attendance.metric_no=subject.metric_no)
WHERE subject.subject_code='".$_GET["subject"]."' AND (attendance.id IS NULL)
GROUP BY subject.metric_no
ORDER BY attendance.enter_time ASC
