Join two tables using period and interval between two dates

Databases
1

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 |
+----------+------+------------+------------+
2

If you use MySQL 15 or newer, you can use CTE and do this in one step. Pseudocode not tested:

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

UPDATE dotable2 SET access_denied = count
WHERE dotable2.user in (SELECT user FROM list)

Basically this is 2 queries in one. First you create a list where you gather all needed information.

Then you update the dotable2 based on the content of the list.

The WITH statement is powerful, and I am surprised that it is not used more often. And you can direct update the record based on the result of CTE.

1 Like
3

Thanks for the reply, really appreciated.

But there are two problems:

  1. the first is that for this occasion they make the version 5.1.51-community of MySQL :frowning:

  2. your query requires manual entry of the dates included in the time range, which I would like to avoid because I cannot control the days on which the user is blocked on a user-by-user

4

I am going to quit my 2 sites that are using MySQL, so I have less experience from MySQL.

https://dev.mysql.com/doc/refman/8.4/en/with.html

I do not think so. You can add the dates in the update, but you have to add dates in the WITH statement. (still pseudocode)

SELECT
	COUNT(*),
	`user`,
        `date1`,
        `date2`
FROM
	`dotable1`
etc
...
UPDATE dotable2 SET access_denied = list.count, date1=list.date1...
1 Like
5

You are trying to produce a COUNT() per user, per date range. You would also need to group by date1

If you literally mean storing the count value in a column in dotable2, NO. This is derived data. You do not store it. You query every time it is needed. For example, if a user is currently blocked and they attempt to access the site, that would add a row to dotable1 and the count value in dotable2 is now incorrect and out of sync with the actual data.

1 Like
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.