By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE tbl1 (col1, col2, tbl1_id) AS
SELECT 'A', 'B', 1 FROM DUAL UNION ALL
SELECT 'A1', 'B1', 2 FROM DUAL;
2 rows affected
CREATE TABLE tbl2 (col3, tbl2_id) AS
SELECT 'C', 1 FROM DUAL UNION ALL
SELECT 'C1', 2 FROM DUAL UNION ALL
SELECT 'C2', 2 FROM DUAL;
3 rows affected
SELECT A.COL1,
A.COL2,
CASE
WHEN COUNT(B.COL3) > 1
THEN 'More than one Value'
ELSE MAX(B.COL3)
END AS COL3
FROM TBL1 A
RIGHT JOIN TBL2 B
ON A.TBL1_ID = B.TBL2_ID
GROUP BY A.COL1, A.COL2;
| COL1 | COL2 | COL3 |
|---|---|---|
| A | B | C |
| A1 | B1 | More than one Value |