Problems in Right join in SQl

Question

I have the following table structure also i have mention my expected output please help me with query as i dont know much about sql query

Table Structure

Table 1 : Emp Details

    FName      Id  
    Pratik      1     
    Praveen     3     
    Nilesh      2     

Table 1 : JoinigDocument

id    DocumentName
1     Leaving
2     Exp letter
3     birth cert

Table 2 : EmployeeJoiningDocument

EmpId  JoiningDocumentId
1        1
1        2
3        1
3        2
3        3
2        1
2        3

Expected Output :

FName      Id  JoiningDocumentId  DocumentName   
Pratik      1     1                  Leaving         
Pratik      1     2                  Exp letter      
Pratik      1     null               birth cert      
Praveen     3     1                  Leaving        
Praveen     3     2                  Exp letter      
Praveen     3     3                  birth cert    
Nilesh      2     1                  Leaving       
Nilesh      2     null               Exp letter      
Nilesh      2     3                  birth cert     

Answer 1

You can write a query as:

select  
       A.FName,
       A.Id,
       B.JoiningDocumentId,
       c.DocumentName
from @JoinigDocument C
cross join @EmployeeDetail A
Left join @EmployeeJoiningDocument B on B.EmployeeId = A.id and 
B.JoiningDocumentId = C.id
order by A.Id

First cross join JoinigDocument and EmployeeDetail table so that you get all possible combinations of Employee and Documents irrespective of the fact that employee has that Joining Document or not. Then you need to do a left join to retain all these matches and find data corresponding to valid entries in EmployeeJoiningDocument.

Demo