subquery problem

cbeshears · #1 (**permalink**) 09-08-06, 14:17

I have three tables that I am using to track who attends meetings on a particular date:

Code:

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.

Code:

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.

r937 · #2 (**permalink**) 09-11-06, 10:57

Code:

SELECT people.* 
  FROM people 
LEFT 
  JOIN attendance 
    ON attendance.person_ID  = people.person_ID
  and attendance.date_ID = 937
ORDER 
    BY people.lname
     , people.fname

why do you have a surrogate key for dates?

cbeshears · #3 (**permalink**) 09-11-06, 16:33

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:

Code:

PEOPLE - person_ID, name, address
DATE - date_ID, date
ATTENDANCE - person_ID (fk), date_ID (fk)

I appreciate your appreciate. And thanks in advance.

r937 · #4 (**permalink**) 09-11-06, 18:33

merging this thread with your other one ...

Internet Services

Web Development

Digital Marketing

Consumer Tech