How can I select data that bigger than specified date (last requested date) and exclude if both created_at AND deleted_at is bigger than a specified date?
For example the last_requested_date is '2017-03-09 15:41:00'. Then, I want to show country that are created/updated/deleted after '2017-03-09 15:41:00'. But I don't want to show country who has been created and then deleted after the last_requested_date (example created_at '2017-03-09 15:41:01' and deleted_at '2017-03-09 15:41:02')
My code is like:
SELECT * FROM countries
WHERE (created_at > '2017-03-09 15:41:00' OR
updated_at > '2017-03-09 15:41:00' OR
deleted_at > '2017-03-09 15:41:00') AND
NOT (created_at > '2017-03-09 15:41:00' AND deleted_at > '2017-03-09 15:41:00')
But the output is not was I expected. Any solution will be appreciate! Thank you
SELECT * FROM countries
WHERE (created_at > '2017-03-09 15:41:00' OR
updated_at > '2017-03-09 15:41:00' OR
deleted_at > '2017-03-09 15:41:00') AND id NOT IN
(SELECT id FROM countries where created_at > '2017-03-09 15:41:00' AND deleted_at > '2017-03-09 15:41:00')