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?.
WITH yourTable AS (
SELECT 209 AS workshop_class_id, 29 AS workshop_session_id, '2021-06-20'::date AS workshop_class_date UNION ALL
SELECT 210, 29, '2021-06-21'::date UNION ALL
SELECT 213, 31, '2021-06-30'::date UNION ALL
SELECT 211, 30, '2021-06-30'::date UNION ALL
SELECT 214, 31, '2021-07-01'::date UNION ALL
SELECT 212, 30, '2021-07-03'::date UNION ALL
SELECT 219, 33, '2021-07-12'::date
)
SELECT t1.*
FROM yourTable t1
WHERE
DATE_TRUNC('month', t1.workshop_class_date)::date = '2021-06-01' OR
EXISTS (SELECT 1 FROM yourTable t2
WHERE t2.workshop_session_id = t1.workshop_session_id AND
t2.workshop_class_id <> t1.workshop_class_id AND
DATE_TRUNC('month', t2.workshop_class_date)::date = '2021-06-01');
| workshop_class_id | workshop_session_id | workshop_class_date |
|---|---|---|
| 209 | 29 | 2021-06-20 |
| 210 | 29 | 2021-06-21 |
| 213 | 31 | 2021-06-30 |
| 211 | 30 | 2021-06-30 |
| 214 | 31 | 2021-07-01 |
| 212 | 30 | 2021-07-03 |