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 |