How to retrieve a list of results that are NOT in another list with SQL

I have a normalized SQL database. The relevant schema looks like this:

User
---
ID,
EmailAddress,
CategoryID

EmailLog
--------
ID,
CategoryID,
UserID,
SentOn

I need to get a list of users that have not been sent an email today for a specific category. Based on my understanding, I need to perform a LEFT JOIN. In an attempt to do this, I’ve tried the following:

DECLARE @Today AS DATETIME;
SET @Today = GETUTCDATE();

DECLARE @CategoryID AS INT;
SET @CategoryID = 101;

SELECT
  User.ID,
  User.EmailAddress,
FROM 
  (SELECT u.ID, u.EmailAddress, u.CategoryID FROM [dbo].[User] u WHERE u.CategoryID=@CategoryID) AS User
  LEFT JOIN
  (SELECT l.CategoryID l.SentOn FROM [dbo].[EmailLog] l WHERE l.CategoryID=@CategoryID AND DAY(l.SentOn)=DAY(@Today) AND MONTH(l.SentOn)=MONTH(@Today) AND YEAR(l.SentOn)=YEAR(@Today)) AS Log
  ON User.UserID = Log.UserID
WHERE
  Log.SentOn IS NULL

I’m using subqueries in this question because my actual query is more complex. However, I have verified that each subquery returns the results I expected. In other words, the first subquery returns a User list. The second subquery returns an EmailLog list of emails sent today.

I’m stuck on the part where I retrieve the users that have NOT been sent an email today though. It’s like I’m trying to prove a negative. What am I missing?

Thank you!

Answer

I would use not exists:

select u.*
from users u
where not exists (select 1
                  from emails e
                  where e.userid = u.id and
                        e.category = @CategoryID and
                        convert(date, e.senton) = convert(date, getutcdate())
                 );

I’m not sure why you are using the UTC date. I would expect the local date/time to make more sense, but this is from your sample code.