Display all employee.

Question

Below is data and query attached21355-q.txt, i want to display all employee from #tbl_employee_Master ,but asd.sal_ID is null from #tbl_assign_Allowance_Deduction ,means that emp_ID exit or not in #tbl_assign_Allowance_Deduction table
,

Answer 1

This is a duplicate of the same question you asked on dba.stackexchange.com. Below is a solution with the ASD.sal_ID IS NULL criteria moved from the WHERE clause to the LEFT JOIN ON clause so that employees with non-null sal_ID values are included.

SELECT
      M.Emp_ID
    , M.EMp_Name 
    , M.EMP_Salary 
    , D.Designation_Name AS Designation
    , SUM(CASE T.DA_Name  WHEN 'Loan' THEN (ASD.assign_amt) ELSE 0 END) Loan
    , SUM(CASE T.DA_Name  WHEN 'Advance' THEN (ASD.assign_amt) ELSE 0 END) Advance
    , SUM(CASE T.DA_Name  WHEN 'Mobile' THEN (ASD.assign_amt) ELSE 0 END) Mobile
    , SUM(CASE T.DA_Name  WHEN 'Conveyance' THEN (ASD.assign_amt) ELSE 0 END) Conveyance
    , SUM(CASE T.DA_Name  WHEN 'Medical' THEN (ASD.assign_amt) ELSE 0 END) Medical
    , SUM(CASE T.DA_Name  WHEN 'Rent' THEN (ASD.assign_amt) ELSE 0 END) House_Rent
    , ISNULL(M.emp_OT,0) AS emp_OT
    , 0 AS 'txtpresent'
    , 0 AS 'txtabsent'
    , 0 AS 'txtsalary'
    , 0 AS 'lbOT_Amount'
    , 0 AS 'txthour'
    , ASD.Sal_ID
FROM #tbl_Employee_Master M
INNER JOIN #tbl_designation D ON D.Designation_ID=M.Designation_ID
LEFT JOIN #tbl_assign_Allowance_Deduction ASD ON asd.Emp_ID=M.Emp_ID AND ASD.sal_ID IS NULL
LEFT JOIN #tbl_Type_allowance_Deduction T ON T.DA_ID=ASD.DA_ID
GROUP BY
      M.Emp_ID
    , M.EMp_Name
    , M.EMP_Salary
    , D.Designation_Name
    , M.emp_OT
    , ASD.Sal_ID;

Answer 2

Table M is linked to the ASD table by LEFT JOIN.
Therefore, for rows of M that do not match in ASD, the sal_ID column is NULL.
Check if the INNER JOIN for ASD satisfies you.