source table AAA
--------------------

pvn_acct_id
acct_id

no primary key, there can be duplicates, for ex:

pvn_acct_id acct_id
======= =========
111 111
111 111
111 111

222 333
222 334
222 335

333 222
334 222
335 222

from first set, using the below query to extract data, it will result in 111,111, for 2nd set, 222, 335, for third set, 335, 222.

select distinct a.pvn_acct_id, a.acct_id
from owner.AAA where a.acct_id = (select max(b.acct_id) from tstg.t_acct_num_hist b
where a.pvn_acct_id = b.pvn_acct_id)
and a.pvn_acct_id = (select max(c.pvn_acct_id) from tstg.t_acct_num_hist c
where a.acct_id = c.acct_id)

Above query is directly populating the target table BBB

What I want to find out is a sample of data set where i can find different instances of acct_id where pvn_Acct_id is same, and vice versa.
For ex:

I want to know where pvn_acct_id is 222, acct_id is 333,334,335, not just acct_id=335.

pls help.

Did you try this:

select pvn_Acct_id from owner.AAA aaa where acct_id in (select acct_id from owner.BBB bbb
where aaa.pvn_acct_id != bbb.pvn_acct_id) order by pvn_Acct_id

select Acct_id from owner.AAA aaa where pvn_acct_id in (select pvn_acct_id from owner.BBB bbb
where aaa.acct_id != bbb.acct_id) order by Acct_id

now this query works. got it with trial and error. have fun testing.