retrieve available day from date rante

Question

I think I'm pretty close on this query, but can't seem to crack it, and I'm not sure if I've got the most efficient approach.

I am trying to find a day where a user is not booked from a range of dates where they are booked.

Think staff scheduling. I need to find who is available to work on Tuesday, and is working on other days this week.

My query currently looks like this

SELECT employees.uid, name, date
FROM employees
LEFT JOIN storelocation ON employees.uid = storelocation.uid
LEFT JOIN schedule ON emplyees.uid = schedule.uid
WHERE slid =9308
AND date
BETWEEN '2009-11-10'
AND '2009-12-20'
AND NOT
EXISTS (

SELECT uid
FROM schedule
WHERE date = '2009-11-11'
)

If I don't include the 'Not Exists', I get 1500 results If I use only the Select form the 'Not Exists', I get 200 results, so both of those queries work independently. However, my query as I've written it returns 0 results.

Answer 1

You might want to try something more like this:

SELECT employees.uid, name, date
FROM users
LEFT JOIN storelocation ON employees.uid = storelocation.uid
LEFT JOIN schedule ON emplyees.uid = schedule.uid
WHERE slid =9308
AND date BETWEEN '2009-11-10' AND '2009-12-20'
AND employees.uid NOT IN (
    SELECT uid
    FROM schedule
    WHERE date = '2009-11-11'
)

Answer 2

The problem is your NOT EXISTS isn't correllated, and you won't be able to do with this without using table aliases:

   SELECT e.uid, 
          e.name, 
          s.date
     FROM EMPLOYEES e
LEFT JOIN STORELOCATION sl ON sl.uid = e.uid
LEFT JOIN schedule s ON s.uid = e.uid
                    AND s.date BETWEEN '2009-11-10'AND '2009-12-20'
    WHERE slid = 9308          
      AND NOT EXISTS (SELECT NULL
                        FROM SCHEDULE t
                       WHERE t.uid = e.uid
                         AND t.date = '2009-11-11')

The SELECT in an EXISTS clause doesn't do anything - you could use EXISTS( SELECT 1/0 ..., which should cause a "can not divide by zero" error. But it won't... EXISTS only returns true if 1+ instances match the WHERE/etc clause. There are numerous questions on SO asking about if it matters what's in the SELECT clause if you want to read more.

Jim's answer, typo aside, should be faster in MySQL than the alternative I supplied. To read more about why, check this article: NOT IN vs NOT EXISTS vs LEFT JOIN/IS NULL in MySQL.