By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (assignment_number, element_name) AS
SELECT 'E11111', 'Basic Life' FROM DUAL UNION ALL
SELECT 'E11111', 'Medical' FROM DUAL UNION ALL
SELECT 'E11111', 'US Social' FROM DUAL UNION ALL
SELECT 'E11111-2', 'Basic Life' FROM DUAL UNION ALL
SELECT 'E11111-2', 'Medical' FROM DUAL UNION ALL
SELECT 'E11111-2', 'US Social' FROM DUAL UNION ALL
SELECT 'E11111-3', 'Basic Life' FROM DUAL;
7 rows affected
CREATE TABLE table2 (element_name) AS
SELECT 'Basic Life' FROM DUAL UNION ALL
SELECT 'Medical' FROM DUAL UNION ALL
SELECT 'US Social' FROM DUAL;
3 rows affected
SELECT t1.assignment_number,
t2.element_name
FROM table2 t2
LEFT OUTER JOIN table1 t1
PARTITION BY (t1.assignment_number)
ON (t1.element_name = t2.element_name)
| ASSIGNMENT_NUMBER | ELEMENT_NAME |
|---|---|
| E11111 | Basic Life |
| E11111 | Medical |
| E11111 | US Social |
| E11111-2 | Basic Life |
| E11111-2 | Medical |
| E11111-2 | US Social |
| E11111-3 | Basic Life |
| E11111-3 | Medical |
| E11111-3 | US Social |