Postgres 8.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table data (irs_number varchar(10), mop_up int, ou varchar(10));
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')
;
8 rows affected

SELECT * FROM data 
WHERE irs_number in (
  SELECT irs_number
  FROM data d
  WHERE EXISTS (SELECT 1
    FROM data 
    WHERE irs_number = d.irs_number
    AND (mop_up = 1 OR d.mop_up = 1)
    AND ou <> d.ou
  )
)


irs_number
mop_up
ou


0001
1
abc

0001
0
abc

0001
0
cde

0001
0
abc