Get data excluding time intervals in another table I have two tables, Message and BlockUser.

Table Message:

ID | Message | DateCreated 
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00 
3  | M3      | 2017-09-21 13:00:00
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00


Table BlockUser:

BlockStartDate       | BlockEndDate
2017-09-21 13:00:00  | 2017-09-22 13:00:00
2017-09-24 13:00:00  | 2017-09-24 14:00:00


Now I want rows from Message excluding those matching the intervals defined in BlockUser. So for my example the result should be:

ID | Message | DateCreated 
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00 
5  | M3      | 2017-09-23 13:00:00
7  | M3      | 2017-09-25 13:00:00


I tried using this query but it didn't work:

WITH CTE AS (
         SELECT BlockStartDate,BlockEndDate FROM BlockUser
         )
SELECT * FROM Message 
WHERE DateCreated NOT BETWEEN 
(SELECT BlockStartDate FROM CTE) 
AND 
(SELECT BlockEndDate  FROM CTE)


It gives me this error:


  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
  an expression.
This is a classic anti-join scenario. You want rows from one table excluding those matching another table.

In English, you could put the condition like this:


  Select every row from Message for which there is no matching row in BlockUser.


In SQL, one way of expressing the above is like this:

SELECT
  *
FROM
  dbo.Message AS m
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      dbo.BlockUser AS bu
    WHERE
      m.DateCreated &gt;= bu.BlockStartDate
      AND m.DateCreated &lt; bu.BlockEndDate
  )
;