I have three tables that I am using to track who attends meetings on a particular date:
people - person_ID, lname, fname
date - date_id, date
attendance - attendance_ID, person_ID, date_ID
Since the meetings are held once I week at the same location, I only need to track the date of the meeting. But my problem is I need to show who was NOT present at the meeting on a particular date. I am using the following query since I am using MySQL Version 4.0.25-standard. Evidentially, I have to have version 4.1 to use the NOT IN query.
SELECT people.* FROM people LEFT JOIN attendance ON people.person_ID=attendance.person_ID WHERE attendance.date_ID IS NULL ORDER BY people.lname, people.fname;
My problem is I need to find out who did not show up for a particular date. The query I have above shows everybody in the database who has never shown up for any meeting. I can't figure out what I'm doing wrong. Any suggestions? Any help is appreciate. And thanks in advance.
I'm fairly new to databases and am trying to understand how to do the following:
I have a form that enters a person's contact information when they attend a meeting (name, address, phone number, etc.). What I need is for the person's ID number (auto incremented) to be duplicated in my attendance table as a foreign key with the current date. How do I do this? How I get the person_ID to enter in both the people table and the attendance table?
Below is how my tables are set up:
PEOPLE - person_ID, name, address
DATE - date_ID, date
ATTENDANCE - person_ID (fk), date_ID (fk)
I appreciate your appreciate. And thanks in advance.