Would anyone be able to help me with the following MySQL problem. I am
trying to extract all of the users who are not on vacation for a given
date. I am using a NOT IN statement, however, I need to have a more
advanced query than a simple one. What I need to do is as follows

SELECT users.user_id,
users.last_name,
users.first_name,
users.active_state
FROM users
WHERE users.active_state = 1 AND users.user_id not in (SELECT
call_schedule.user_id,
call_schedule.callLocation_id,
call_schedule.callSchedule_id,
call_locations.callLocation_DisplayName,
call_locations.callLocation_id,
call_locations.postCallStatus,
call_schedule.callDate
FROM call_locations INNER JOIN call_schedule ON
call_locations.callLocation_id = call_schedule.callLocation_id
WHERE call_locations.postCallStatus = 0 AND call_schedule.callDate =
'2007-08-21')

This query does not work since I am only allowed to select one field
in the NOT IN query. Would anyone be able to provide some guidance on
how to make this query work.

I have tried to duplicate this with the following LEFT JOIN

SELECT users.last_name,
users.first_name,
users.active_state,
call_schedule.callLocation_id,
call_schedule.callDate,
call_locations.callLocation_DisplayName,
call_locations.postCallStatus
FROM call_schedule LEFT OUTER JOIN users ON call_schedule.user_id = users.user_id
INNER JOIN call_locations ON call_locations.callLocation_id = call_schedule.callLocation_id
WHERE users.active_state = 1 AND call_locations.postCallStatus = 1 AND call_schedule.callDate = '2007-08-21'

However, this does not return any of the users who are NOT scheduled on the give day selected

Can anyone please give me some guidance on how I can obtain the users who are not scheduled on a given day.

Thank you in advance for any help with this problem

Sincerely,

Don