Rails/MYSQL - Find all with no association

Question

I have an Employee model and a Shift model. Employees have many shifts and shifts have a date.

I want to be able to select all employees that are available for a given date i.e. select all employees where no associated shifts exists for a given date.

I can select all employees without a shift like this:

SELECT users.* FROM users
LEFT JOIN shifts ON users.id = shifts.user_id
WHERE shifts.user_id IS NULL;

But this is not date specific.

Thanks.

Answer 1

SELECT users.* 
FROM users 
LEFT JOIN shifts ON users.id = shifts.user_id 
    AND shifts.Date = @SomeDate
WHERE shifts.user_id IS NULL;