我有四张table

applicationmaster, projectdetails, clientdetails, employeedetails

我有一个存储过程通过连接这四个表来返回所有列，比如-applicationmaster可能有employee，也可能没有employee。
applicationmaster和employee details都用app\u idMap。如果app\u id存在，我想从employeedetails表中检索所有数据；如果app\u id不存在，则为employeedetails的所有列返回null

applicationmaster                                   employeedetails

  app_id | app_name | cotractor                       emp_id | app_id | emp_name | emp_designation
   1         BPM       X                              101        1      Tony           Manager
   2         CRM       Y                              102        1      Mark           Consultant
   3         Portal    Z

这里，我有两个应用程序id 1的员工。所以我想找回：

1  BPM   X   101     Tony    Manager
   1  BPM   X   102     Mark    Consultant
   2  CRM   Y   null    null    null
   3  Portal Z  null    null    null

但是，我的存储过程只返回applicationmaster表中包含雇员的那些行。
就像只从存储过程返回前两行一样。

ALTER PROCEDURE [dbo].[sp_Dashboard_SowReport]
AS  
BEGIN 
    SELECT
        am.app_id AS 'App Id',
        cd.app_name AS 'App Name',
        cd.scheduler_name AS 'Scheduler Name',
        pd.project_name AS 'Project Name',
        pd.Project_id AS 'Project Id',
        MONTH(am.app_end_date) AS 'App Expiring Month',
        DATEDIFF(Day,am.app_start_date,am.app_end_date) AS 'Contract due in days',
        ed.associate_name AS 'Associate Name',
        ed.emp_id AS 'EID',
        ed.client_id AS 'Client Id',
        ed.bill_rate_per_hour AS 'Bill Rate'
    FROM
        applicationMaster am
    JOIN
        projectdetails pd ON (am.project_id = pd.project_id)
    JOIN
        clientdetails cd ON (cd.client_id = pd.client_id)
    JOIN 
        employeedetails ed ON (ed.app_id = am.app_id)
END

有人能帮帮我吗？我正在学习编写和优化sql查询
谢谢您