By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table your_table (message_id int, concerned_user varchar(30))
insert into your_table (message_id, concerned_user) values
(1, 'laura' ),
(1, 'vick' ),
(1, 'john' ),
(2, 'laura' ),
(2, 'vick' );
5 rows affected
SELECT message_id
FROM your_table
GROUP BY message_id
HAVING COUNT(DISTINCT concerned_user) = 2
AND COUNT(CASE WHEN concerned_user = 'laura' THEN 1 END) > 0
AND COUNT(CASE WHEN concerned_user = 'vick' THEN 1 END) > 0;
| message_id |
|---|
| 2 |