Syntax `WHERE NOT EXISTS` using MySQL

Question

This is my table tb_group on DB MySQL

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

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…

To find missing groups in the table tb_group 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 tb_group the values ​​of the _tgroup missing in the table tb_group 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

I have tried this solution fiddle demo

I have this problem:

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

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...

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

Solution fiddle demo

Answer 1

To achieve your goal of inserting missing _tgroup values from ta_group into tb_group for every distinct _tdate present in tb_group, you'll need to generate a cross join between all dates and all groups, then filter out those already existing in tb_group.

INSERT INTO tb_group (_tdate, _tgroup, _tnumber)
SELECT 
    d._tdate,
    g._tgroup,
    0 AS _tnumber
FROM 
    (SELECT DISTINCT _tdate FROM tb_group) d
CROSS JOIN 
    ta_group g
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM tb_group t 
        WHERE t._tdate = d._tdate AND t._tgroup = g._tgroup
    );

Explanation:

• (SELECT DISTINCT _tdate FROM tb_group): Gets all unique dates from your existing table.
• CROSS JOIN ta_group: Pairs each date with every group in ta_group, forming all possible combinations.
• NOT EXISTS: Ensures you only insert combinations that don't already exist in tb_group.
• _tnumber = 0: You insert 0 for missing records as required.

📌 Example:

• Given:
  • Dates in tb_group: 2025-05-12, 2025-05-13
  • Groups in ta_group: 71AB1, 71AB2, 71AB3
• The query will check for every combination like:
  2025-05-12, 71AB1
2025-05-12, 71AB2
2025-05-13, 71AB1
  …and so on.
• It will only insert combinations that are not already present in tb_group.

Answer 2

INSERT IGNORE INTO tb_group
SELECT dateslist._tdate, ta_group._tgroup, 0
FROM ta_group
CROSS JOIN (
  SELECT DISTINCT _tdate
  FROM tb_group
  ) dateslist;
SELECT * FROM tb_group ORDER BY 1, 2;

fiddle

CROSS JOIN generates all (group-date) pairs. INSERT IGNORE inserts only those pairs which are not present in the table yet.

If ta_group._tgroup is not unique then replace the table in the query with the subquery which selects only distinct group codes values.