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?

Stryder · Accepted Answer · 2010-08-03 20:20:42Z

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.

Damian Leszczyński - Vash · Accepted Answer · 2010-08-03 19:51:56Z

0

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

answered Aug 3, 2010 at 19:51

Damian Leszczyński - Vash

31k9 gold badges64 silver badges95 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related