By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test( rid VARCHAR(200), rkey VARCHAR(200), rvalue VARCHAR(200) )
BEGIN
INSERT INTO test (rid, rkey, rvalue) VALUES ('UUID-1', 'editedBy', 'moderator');
INSERT INTO test (rid, rkey, rvalue) VALUES ('UUID-1', 'comments', 'deleted...');
INSERT INTO test (rid, rkey, rvalue) VALUES ('UUID-2', 'editedBy', 'low_moderator');
INSERT INTO test (rid, rkey, rvalue) VALUES ('UUID-2', 'comments', 'deleted...');
INSERT INTO test (rid, rkey, rvalue) VALUES ('UUID-3', 'editedBy', 'admin');
INSERT INTO test (rid, rkey, rvalue) VALUES ('UUID-4', 'editedBy', 'admin2');
END;
/
1 rows affected
SELECT *
FROM test t
WHERE NOT EXISTS ( SELECT 0
FROM test
WHERE rkey = 'comments'
AND rvalue = 'deleted...'
AND rid = t.rid )
| RID | RKEY | RVALUE |
|---|---|---|
| UUID-3 | editedBy | admin |
| UUID-4 | editedBy | admin2 |
WITH t AS
(
SELECT t.*,
SUM(CASE WHEN rkey = 'comments' AND rvalue='deleted...'
THEN 1
ELSE 0
END) OVER (PARTITION BY rid) AS cnt
FROM test t
)
SELECT rid, rkey, rvalue
FROM t
WHERE cnt = 0
| RID | RKEY | RVALUE |
|---|---|---|
| UUID-3 | editedBy | admin |
| UUID-4 | editedBy | admin2 |