Below is data and query, i want to display all employee from #tbl_employee_Master ,but asd.sal_ID is null from #tbl_assign_Allowance_Deduction
Below is image,in which three employee is coming,but i want to display all five employe
<a href="https://ibb.co/tp9YQ3T"></a>
What I have tried:
Drop table #tbl_designation
Drop table #tbl_Type_allowance_Deduction
Drop table #tbl_assign_Allowance_Deduction
Drop table #tbl_Employee_Master
Create Table #tbl_Designation (Designation_ID int, Designation_Name varchar(50));
CREATE TABLE #tbl_Employee_Master (emp_ID INT,emp_Name Varchar(50),emp_salary int,Designation_ID int,emp_OT int );
CREATE TABLE #tbl_Type_allowance_Deduction (DA_ID INT,DA_Name Varchar(50), DA_TYPE_ID Varchar(50));
CREATE TABLE #tbl_assign_Allowance_Deduction (assign_ID INT,EMP_ID int ,DA_ID int, Assign_amt int,Sal_ID int);
Insert into #tbl_Designation values
(1,'officer'),
(2,'Executive')
Insert into #tbl_Employee_Master values
(1001,'Irfan',10000,1,20),
(1002,'Khan',10500,2,20),
(1003,'Akhter',1200,1,20) ,
(1004,'Ali',8000,2,20),
(1005,'Jhain',3300,1,20)
insert into #tbl_Type_allowance_Deduction values
(1,'Loan','Deduction'),
(2,'Medical','Allowance'),
(3,'Rent','Allowance'),
(4,'Conveyance','Allowance'),
(5,'Advance','Deduction'),
(6,'Mobile','Allowance')
insert into #tbl_assign_Allowance_Deduction values
(111,1001,1,500,111),
(112,1001,2,500,111),
(113,1002,3,540,111),
(114,1003,4,200,null),
(115,1003,6,400,null)
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 'txtpresent', 0 'txtabsent',0 'txtsalary',0 'lbOT_Amount',0'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
left outer join #tbl_Type_allowance_Deduction T on T.DA_ID=ASD.DA_ID
where asd.sal_ID is null
group by M.Emp_ID,M.EMp_Name,M.EMP_Salary,D.Designation_Name,M.emp_OT,asd.Sal_ID