4

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 =, !=, <, <= , >, >= or when the subquery is used as an expression.

3 Answers 3

5

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 >= bu.BlockStartDate
      AND m.DateCreated < bu.BlockEndDate
  )
;
3

You could simply try something like:

select m.*
from Message m
left join BlockUser b on m.datecreated between blockstartdate and blockenddate
where b.blockstartdate is null
1
  • Your answer works perfect too!!! Thank you Sep 27, 2017 at 8:37
0

Here what you want.

CREATE TABLE [Message](
ID INT, 
[Message] VARCHAR(10),
DateCreated DATETIME
)
GO
INSERT INTO dbo.Message
        ( ID, Message, DateCreated )

VALUES
(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')
GO
CREATE TABLE BlockUser(
StartDate DATETIME, 
EndDate DATETIME
)
GO
INSERT INTO dbo.BlockUser
        ( StartDate, EndDate )
VALUES  ( 
'2017-09-21 13:00:00',
'2017-09-22 13:00:00'),
('2017-09-24 13:00:00','2017-09-24 14:00:00')

SELECT Message.*
FROM dbo.Message
LEFT JOIN dbo.BlockUser ON DateCreated BETWEEN StartDate AND EndDate
WHERE startDate IS NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.