Hi,
I am looking for help to find a logic to get the desired output.
I have 2 tables
EMP_TBL
EMP_ID DEPT
89898 10
89898 10
89898 10
89898 20
89898 20
89898 20
89898 20
99984 30
99984 30
44444 10
55555 50
EMAIL_TBL
DEPT EMAIL
10 10@gmail.com
20 20@gmail.com
30 30@gmail.com
XXX default@gmail.com
If employee is working in more than one dept or dept does not exist in EMAIL_TBL then i need to fetch the default email from EMAIL_TBL else should get the corresponding dept email as mentioned below
I have tried to write a below sql to get the desired output but it's not working
SELECT et.emp_id,
email,
Dense_rank ()
over (
ORDER BY et.dept_no) AS rnk
FROM emp_tbl et,
email_tbl emtbl
WHERE 1 = 1
AND et.dept_no = emtbl.dept_no(+)
Expected output
EMP_ID EMAIL
89898 default@gmail.com
99984 30@gmail.com
44444 10@gmail.com
55555 default@gmail.com