A
You could use the analytical function FIRST_VALUE along with the clausula DISTINCT to get the result you want. WITH CURSO AS (
SELECT 'C1' CURSO, 'ejemplo1' NOMBRE_CURSO, 'herramientas' AREA, 1 ORDEN FROM DUAL UNION ALL
SELECT 'C1' CURSO, 'ejemplo1' NOMBRE_CURSO, 'aplicaciones' AREA, 2 FROM DUAL UNION ALL
SELECT 'C1' CURSO, 'ejemplo1' NOMBRE_CURSO, 'metodologias' AREA, 3 FROM DUAL UNION ALL
SELECT 'C2' CURSO, 'ejemplo2' NOMBRE_CURSO, 'aplicaciones' AREA, 1 FROM DUAL UNION ALL
SELECT 'C2' CURSO, 'ejemplo2' NOMBRE_CURSO, 'metodologias' AREA, 2 FROM DUAL UNION ALL
SELECT 'C3' CURSO, 'ejemplo3' NOMBRE_CURSO, 'aplicaciones' AREA, 1 FROM DUAL ),
EMPLEADO AS (
SELECT '999' NUMERO_EMPLEADO, 'ANA' NOMBRE, 'C1' CURSO FROM DUAL UNION ALL
SELECT '999' NUMERO_EMPLEADO, 'ANA' NOMBRE, 'C2' CURSO FROM DUAL UNION ALL
SELECT '999' NUMERO_EMPLEADO, 'ANA' NOMBRE, 'C3' CURSO FROM DUAL )
SELECT DISTINCT E.NUMERO_EMPLEADO,
E.NOMBRE,
C.CURSO,
FIRST_VALUE(C.AREA) OVER (PARTITION BY C.CURSO ORDER BY C.ORDEN RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CURSO
FROM CURSO C
JOIN EMPLEADO E ON C.CURSO = E.CURSO
Focus on the last SELECT That's the one you'd have to use. The WITH It's just to simulate the tables of your example.Keep in mind that I made a modification on your EMPLEADO table, as I think you have a mistake there, because the way your example is raised, they would never get in the JOIN a registration with course 3 (C3). Basically, change the last record of your C2 to C3 employee board.Finally, as you did not indicate that order should be used to bring the first record, I added an ORDER column in the CURSO table. If you prefer, you could use alphabetic order by modifying the query.SELECT DISTINCT E.NUMERO_EMPLEADO,
E.NOMBRE,
C.CURSO,
FIRST_VALUE(C.AREA) OVER (PARTITION BY C.CURSO ORDER BY C.AREA RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS CURSO
FROM CURSO C
JOIN EMPLEADO E ON C.CURSO = E.CURSO
Greetings