Select rows whose IDs are not found in another table under certain constraints

Question

The practical problem I'm solving is to display a list of items that have been updated recently that the user has not viewed recently.

I'm trying to return a table containing items that are not containing in an item views table for a given user (let's say for this case user number 1). I've come up with:

SELECT * FROM items i
WHERE i.updated_at > date_sub(curdate(), interval 10 day)
AND i.id NOT IN (
  SELECT item_id FROM item_views v
  WHERE i.created_at > date_sub(curdate(), interval 10 day)
  AND i.user_id = 1
)

This seems to work fine (is it even the best way to do it?). However, I run into issues when considering an item that was viewed 8 days ago and updated 3 days ago. Such an item is clearly new, but it wouldn't show up under this query. How should I approach adding this restriction?

Answer 1

This is not optimal solution. But fast anwser replace the AND with OR should work.

Answer 2

If you have the updated_at column in your view you could always add a line to check to make sure the updated date is not within your timeline. Better yet you could check to make sure your updated date is greater than your last viewed date.