By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE data(
irs_number VARCHAR (50),
mop_up INTEGER,
ou VARCHAR (50)
)
INSERT INTO data VALUES
('0001', 1, 'abc'),
('0001', 0, 'abc'),
('0001', 0, 'cde'),
('0001', 0, 'abc'),
('0002', 1, 'abc'),
('0002', 0, 'abc'),
('0003', 0, 'abc'),
('0003', 0, 'xyz')
Records: 8 Duplicates: 0 Warnings: 0
SELECT irs_number, mop_up, ou
FROM data d
WHERE EXISTS (SELECT 1
FROM data d2
WHERE d2.irs_number = d.irs_number AND
d2.mop_up = 1
) AND
EXISTS (SELECT 1
FROM data d2
WHERE d2.irs_number = d.irs_number AND
d2.ou <> d.ou
)
| irs_number | mop_up | ou |
|---|---|---|
| 0001 | 1 | abc |
| 0001 | 0 | abc |
| 0001 | 0 | cde |
| 0001 | 0 | abc |