Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    6

    Unanswered: subquery problem

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    Posts
    6

    duplicate IDs

    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    merging this thread with your other one ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •