This is a secondary table (dotable2) for each user a time interval is stored in which the user was blocked.

In this example the user Will has been blocked three times since his registration, once in 2021 and twice in 2023.

+------------+------------+------+
| date1      | date2      | user |
+------------+------------+------+
| 2023-02-08 | 2023-02-12 | Will |
| 2023-07-12 | 2023-07-28 | Will |
| 2021-06-18 | 2021-06-26 | Will |
+------------+------------+------+

In the main access registration table (dotable1) the user Will in the time interval (2023-07-12 ; 2023-07-28) in which he was blocked tried to access 613 times

SELECT
    COUNT(*),
    `user`,
FROM
    `dotable1`
WHERE
    `user` = 'Will' AND `blocked` = 'Y' AND access_date BETWEEN '2023-07-12' AND '2023-07-28';

+----------+------+
| COUNT(*) | user |
+----------+------+
|      613 | Will |
+----------+------+

Now I need update the secondary table (dotable2) for this output

+------------+------------+------+---------------+
| date1      | date2      | user | access_denied |
+------------+------------+------+---------------+
| 2023-07-12 | 2023-07-28 | Will |     613       |
+------------+------------+------+---------------+

But when I try to join the two tables the output is wrong, how to do resolve this?

Thanks

SELECT
    COUNT(*),
    asr.user,
    emr.date1,
    emr.date2 
FROM
    dotable1 asr JOIN dotable2 emr ON asr.user = emr.user AND asr.access_date BETWEEN emr.date1 AND emr.date2 
WHERE
    asr.user = 'Will' AND asr.blocked = 'Y' AND asr.access_date BETWEEN emr.date1 AND emr.date2 
GROUP BY
    asr.user;

+----------+------+------------+------------+
| COUNT(*) | user | date1      | date2      |
+----------+------+------------+------------+
|     1388 | Will | 2023-02-08 | 2023-02-12 |
+----------+------+------------+------------+

Now I need update the secondary table (dotable2) for this output

+------------+------------+------+---------------+
| date1      | date2      | user | access_denied |
+------------+------------+------+---------------+
| 2023-07-12 | 2023-07-28 | Will |     613       |
+------------+------------+------+---------------+

This is a secondary table (dotable2) for each user a time interval is stored in which the user was blocked.

In this example the user Will has been blocked three times since his registration, once in 2021 and twice in 2023.

+------------+------------+------+
| date1      | date2      | user |
+------------+------------+------+
| 2023-02-08 | 2023-02-12 | Will |
| 2023-07-12 | 2023-07-28 | Will |
| 2021-06-18 | 2021-06-26 | Will |
+------------+------------+------+

In the main access registration table (dotable1) the user Will in the time interval (2023-07-12 ; 2023-07-28) in which he was blocked tried to access 613 times

SELECT
    COUNT(*),
    `user`,
FROM
    `dotable1`
WHERE
    `user` = 'Will' AND `blocked` = 'Y' AND access_date BETWEEN '2023-07-12' AND '2023-07-28';

+----------+------+
| COUNT(*) | user |
+----------+------+
|      613 | Will |
+----------+------+

Now I need update the secondary table (dotable2) for this output

+------------+------------+------+---------------+
| date1      | date2      | user | access_denied |
+------------+------------+------+---------------+
| 2023-07-12 | 2023-07-28 | Will |     613       |
+------------+------------+------+---------------+

But when I try to join the two tables the output is wrong, how to do resolve this?

Thanks

SELECT
    COUNT(*),
    asr.user,
    emr.date1,
    emr.date2 
FROM
    dotable1 asr JOIN dotable2 emr ON asr.user = emr.user AND asr.access_date BETWEEN emr.date1 AND emr.date2 
WHERE
    asr.user = 'Will' AND asr.blocked = 'Y' AND asr.access_date BETWEEN emr.date1 AND emr.date2 
GROUP BY
    asr.user;

+----------+------+------------+------------+
| COUNT(*) | user | date1      | date2      |
+----------+------+------------+------------+
|     1388 | Will | 2023-02-08 | 2023-02-12 |
+----------+------+------------+------------+

Now I need update the secondary table (dotable2) for this output

+------------+------------+------+---------------+
| date1      | date2      | user | access_denied |
+------------+------------+------+---------------+
| 2023-07-12 | 2023-07-28 | Will |     613       |
+------------+------------+------+---------------+

• mysql

• You don't need the same BETWEEN condition in the ON and WHERE clauses. – Barmar Commented Mar 7 at 21:52
• You need GROUP BY asr.user, emr.date1, emr.date2. Otherwise you're getting the total of all their denials, not just the count for each block. – Barmar Commented Mar 7 at 21:55

1 Answer 1

Based on what is mentioned in comments,you need to include emr.date1 and emr.date2 in GROUP BY to count the number of blocks in that period

SELECT emr.date1,emr.date2,asr.user, COUNT(*) AS access_denied
FROM dotable1 asr
INNER JOIN dotable2 emr ON
asr.user = emr.user AND asr.access_date BETWEEN emr.date1 AND emr.date2
WHERE
asr.user = 'Will' AND asr.blocked = 'Y'
GROUP BY
emr.date1, emr.date2, asr.user

You can see the fiddle demo here and also the update statement based on sample dataset.