List all employee with leave title, and leave apply in join

Question

I have three tables:

Table 1: employee

emp_id -KEY    emp_name    emp_join_date
 1841           test       2016-04-01
 1842           test1      2019-04-01

Table 2: leave_set

emp_id-KEY    start_date    end_date    nod    leave_type_id
 1841         2019-04-01    2019-04-02   2         2

Table 3: leave_type

leave_type_id-KEY    leave_title
    1                   AL
    2                   SL
    3                   CME

I tried left join but if leave_set has no values then it return null values in Leave_Set and leave_type.

   SELECT 
       O.emp_id,  
       P.leave_title, I.leave_type_id, I.nod
   FROM 
       employee O 
   LEFT JOIN 
       leave_set I ON O.emp_id = I.emp_id and o.emp_id=1841
   LEFT OUTER JOIN 
       leave_type P ON P.leave_type_id = I.leave_type_id 

My output looks like this:

emp_id  leave_title   nod
1841    NULL          NULL
1879    NULL          NULL

I need output like below:

emp_id   Leave_title  nod
1841     AL           -
1841     SL           2
1841     CME          -
1842     AL           -
1842     SL           -
1842     CME          -

I need each employee with all leave_title with nod whether they apply leave_set.

Answer 1

I think you want a cross join with leave_type because you want all combinations. Followed by a left join of the actual leave record e.g.

select O.emp_id, P.leave_title, I.leave_type_id, I.nod
from employee O 
cross join leave_type P 
left join leave_set I ON O.emp_id = I.emp_id and I.leave_type_id = P.leave_type_id
where o.emp_id = 1841

PS: You would normally restrict to the employee in question in the where clause, not the join.