Syntax `WHERE NOT EXISTS` using MySQL

Databases
1

Hello everyone, I need your help.

This is my table tb_group on DB MySQL

+------------+---------+----------+
| _tdate     | _tgroup | _tnumber |
+------------+---------+----------+
| 2025-05-12 | 71AB5   |       1  |
| 2025-05-12 | 71DB1   |       1  |
+------------+--------+-----------+

To find missing groups in the table _tgroup I use the syntax WHERE NOT EXISTS by crossing the values ​​of the _tgroup columns of the two tables ta_group and tb_group.

SELECT
	`_tgroup` 
FROM
	`ta_group` q 
WHERE
	NOT EXISTS ( SELECT 1 FROM `tb_group` t WHERE q.`_tgroup` = t.`_tgroup` );

This is the result

+---------+
| _tgroup |
+---------+
| 71AB3   |
| 71AB4   |
| 71AB6   |
| 71DB2   |
| 71DB4   |
| 71DB6   |
+---------+

Now I need insert into my table _tgroup the values ​​of the _tgroup missing in the table _tgroup for this output

+------------+---------+----------+
| _tdate     | _tgroup | _tnumber |
+------------+---------+----------+
| 2025-05-12 | 71AB3   |       0  |
| 2025-05-12 | 71AB4   |       0  |
| 2025-05-12 | 71AB5   |       1  |
| 2025-05-12 | 71AB6   |       0  |
| 2025-05-12 | 71DB1   |       1  |
| 2025-05-12 | 71DB2   |       0  |
| 2025-05-12 | 71DB4   |       0  |
| 2025-05-12 | 71DB6   |       0  |
+------------+---------+----------+

How to do resolve this?

Thanks in advance for any help

2

does tb_group have a default value defined for _tdate and _tnumber?

does tb_group have a primary or unique key set on _tgroup?

1 Like
3

The values ​​of columns _tdate and _tnumber of the table tb_group are obtained from a table where accesses are recorded for individual groups, therefore in column _tdate you find the access date, in column _tnumber you find the number of accesses that occurred with that group… here you find the simulation

4

Step 1: Put some keys on your tables.
Step 2:

INSERT INTO tb_group(_tdate,_tgroup,_tnumber)
SELECT "2025-05-12",_tgroup,0 
FROM ta_group 
ON DUPLICATE KEY 
UPDATE _tnumber = _tnumber
1 Like
5

Okay, thanks for help.

I have this problem:

  1. the date is not just the day 2025-05-12

demo

6

It is in your example. What do you mean?

Your desired outcome was:

1 Like
7

In my simplified example I only reported the data for the day 2025-05-12 but in the tb_group table there are all the days of the year up to yesterday

8

Okay… so… what exactly do you want put into the tb_group table?

1 Like
9

I have to insert into the tb_group table all the groups that are missing in the tb_group table, taking them from the ta_group table, for each day recorded in the tb_group table

demo

10

Theres the part we were missing.

Okay. Time to get stupid with joins.

INSERT INTO tb_group
SELECT tb._tdate,ta._tgroup,0 
FROM ((SELECT DISTINCT _tdate FROM tb_group) tb
LEFT JOIN ta_group ta 
ON 1 = 1
)
ON DUPLICATE KEY
UPDATE _tnumber = _tnumber;

(strictly speaking i’m not sure the outer parentheses or the LEFT designator are technically needed, but… it works.)

1 Like
11

Thanks a lot demo

1 Like