Getting the latest timestamp in Oracle
With A CTE and ROW_NUMBER in conhunction with Cross JOIN you can get your wanted result.
The CTE creates a temporary table, which has the row number for all all master ids and of course the wanted member id.
The SELECTmakes a Cartesian product of the first (latest) member id and the rest of the rows(member ids) for every master id


CREATE TABLE master
    (&quot;id&quot; int, &quot;member_id&quot; int)
;




INSERT ALL 
    INTO master (&quot;id&quot;, &quot;member_id&quot;)
         VALUES (545634, 5655656)
    INTO master (&quot;id&quot;, &quot;member_id&quot;)
         VALUES (545634, 653363)
    INTO master (&quot;id&quot;, &quot;member_id&quot;)
         VALUES (545634, 545454)
SELECT * FROM dual




CREATE TABLE members
    (&quot;member_id&quot; int, &quot;audit_id&quot; int)
;




INSERT ALL 
    INTO members (&quot;member_id&quot;, &quot;audit_id&quot;)
         VALUES (5655656, 656566)
    INTO members (&quot;member_id&quot;, &quot;audit_id&quot;)
         VALUES (653363, 656565)
    INTO members (&quot;member_id&quot;, &quot;audit_id&quot;)
         VALUES (545454, 545245)
SELECT * FROM dual




CREATE TABLE audits
    (&quot;audit_id&quot; int, &quot;update_time&quot; DATE )
;




ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';




INSERT ALL 
    INTO audits (&quot;audit_id&quot;, &quot;update_time&quot;)
         VALUES (656566, '2021-01-15 17:01:34')
    INTO audits (&quot;audit_id&quot;, &quot;update_time&quot;)
         VALUES (656565, '2020-05-13 17:01:34')
    INTO audits (&quot;audit_id&quot;, &quot;update_time&quot;)
         VALUES (545245, '2020-08-11 17:01:34')
SELECT * FROM dual;




WITH CTE AS (SELECT ma.&quot;id&quot;,me.&quot;member_id&quot;,
   Row_NUMBER() OVER (
        PARTITION BY ma.&quot;id&quot;
        ORDER BY &quot;update_time&quot; ASC
                ) &quot;rn&quot;
from master ma 
    INNER JOIN members me on ma.&quot;member_id&quot; = me.&quot;member_id&quot;
    INNER JOIN audits au ON me.&quot;audit_id&quot; = au.&quot;audit_id&quot;)
SELECT a.&quot;member_id&quot;, b.&quot;member_id&quot;
FROM (SELECT &quot;member_id&quot;,&quot;id&quot; FROM CTE WHERE &quot;rn&quot;= 1) a 
CROSS JOIN (SELECT &quot;member_id&quot;,&quot;id&quot;, &quot;rn&quot; FROM CTE WHERE &quot;rn&quot; &gt; 1)  b 
WHERE a.&quot;id&quot; = b.&quot;id&quot;
ORDER BY b.&quot;rn&quot;;


member_id | member_id
--------: | --------:
   653363 |    545454
   653363 |   5655656


db&lt;&gt;fiddle here