Search code examples
mysqlsqlmariadbmariasql

SQL Query to display if two dates is not bigger than a specified date

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

Solution 
  •  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')