1

i want to display all employee from tbl_Employee_Master ,weather emp_Id is exit or not in #tbl_assign_Allowance_Deduction table

I developed a query,but it fetching those record where asd.sal_ID is null,but i want to fetch all employee from tbl_Employee_Master table,when ever this query is run'

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
0

I developed a query,but it fetching those record where asd.sal_ID is null,but i want to fetch all employee from tbl_Employee_Master table,when ever this query is run'

The asd.sal_ID IS NULL criteria in the WHERE clause will exclude all employees with non-null deductions. Move that criteria the the ON clause of the allowance deduction LEFT JOIN to include allowance deduction rows with NULL Sal_ID values without excluding employees with non-null values too:

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.