NOT IN query problem

cbesh2

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:

SELECT * FROM people WHERE person_ID NOT IN (SELECT person_ID from attendance WHERE date_ID=1

but I am getting the following error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT person_ID from attendance WHERE date_ID=1' at line 1

I can't figure out what I'm doing wrong. I am using PHP 4.4.1 so I'm not sure why it's not working. Any suggestions? Any help is appreciate. And thanks in advance.

ahundiak

The query you pasted is missing the right paren.

The php version does not matter but the mysql version does. Sub selects for mysql are a fairly recent addition. Check the mysql manual for details.

cbesh2

Well, I actually have the right parenthesis in my code -- I just neglected to type it in this post. I'm still getting the following error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT person_ID from attendance WHERE date_ID=1)' at line 1

ahundiak

And your mysql version is?

cbesh2

4.0.25-standard

ahundiak

Thats the problem. You need at least 4.1. You might be able to rewrite the query with a left join and then checking for NULL attendence.person_ID values. Google around a bit on mysql subselect for examples.

cbesh2

No wonder. I'll do some searching and see what I can come up with. At least I know what the problem is. Thank you so much!