This question is the continuation of this one.
I have the following table egr:
+---------+------------+
| offid | groupid |
+---------+------------+
| 1 | 101 |
| 1 | 202 |
| 2 | 202 |
| 2 | 404 |
+---------+------------+
I would like to insert missing groupids that the offid 2 does not have (compared to offid 1). Result would be:
+---------+------------+
| offid | groupid |
+---------+------------+
| 1 | 101 |
| 1 | 202 |
| 2 | 202 |
| 2 | 404 |
| 2 | 101 | --> new row to insert
+---------+------------+
My try, based on the answer of my other question (not working):
INSERT INTO egr (offid, groupid)
SELECT 2, egr1.groupid
FROM egr AS egr1
WHERE egr1.offid = 1
AND NOT EXISTS
(select 1
from egr e2
where e2.groupid = egr1.groupid and
e2.offid in (1, 2) and
e2.offid <> egr1.offid
);
This may be easier to achieve using the except operator:
INSERT INTO egr (offid, groupid)
SELECT 2, groupid
FROM egr
WHERE offid = 1
EXCEPT
SELECT 2, groupid
FROM egr
WHERE offid = 2