Oracle 23c

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

CREATE TABLE a_test (id_test VARCHAR2(20));
INSERT INTO a_test VALUES ('AAA');
1 rows affected

INSERT INTO a_test VALUES ('BBB');
1 rows affected

INSERT INTO a_test VALUES ('CCC');
1 rows affected

CREATE TABLE dummy_test (id_first VARCHAR2(20), id_second VARCHAR2(20), id_third VARCHAR2(20));
INSERT INTO dummy_test VALUES ('AAA', 'test', 'test');
1 rows affected

INSERT INTO dummy_test VALUES ('test', 'test', 'BBB');
1 rows affected

INSERT INTO dummy_test VALUES ('CCC', 'AAA', 'test');
1 rows affected

INSERT INTO dummy_test VALUES ('test', 'BBB', 'CCC');
1 rows affected

INSERT INTO dummy_test VALUES ('AAA', 'BBB', 'CCC');
1 rows affected

SELECT * FROM
(SELECT A.*, B.*, 
        ROW_NUMBER() 
            OVER (PARTITION BY B.ROWID 
                  ORDER BY CASE WHEN A.ID_TEST = B.ID_FIRST THEN 1
                                WHEN A.ID_TEST = B.ID_SECOND THEN 2
                                ELSE 3 END) AS RN
  FROM A_TEST A 
  JOIN DUMMY_TEST B ON A.ID_TEST = B.ID_FIRST OR A.ID_TEST = B.ID_SECOND 
                    OR A.ID_TEST = B.ID_THIRD
) T WHERE RN = 1;


ID_TEST
ID_FIRST
ID_SECOND
ID_THIRD
RN


AAA
AAA
test
test
1

BBB
test
test
BBB
1

CCC
CCC
AAA
test
1

BBB
test
BBB
CCC
1

AAA
AAA
BBB
CCC
1

ID_TEST	ID_FIRST	ID_SECOND	ID_THIRD	RN
AAA	AAA	test	test	1
BBB	test	test	BBB	1
CCC	CCC	AAA	test	1
BBB	test	BBB	CCC	1
AAA	AAA	BBB	CCC	1