locked
Display all employee RRS feed

  • Question

  • User-367318540 posted

    i want display all employee from #tbl_Employee_Master table,asd.Sal_ID is null 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'

    below is my query and data

    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

    Sunday, August 30, 2020 9:35 AM

Answers

  • User-1330468790 posted

    Hi akhterr,

     

    If you don't need SAL_ID, why do you add this column in the select query?

    Just remove "SAL_ID" in SELECT part:

    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'  -- remove the sal_id here
    FROM #tbl_Employee_Master M
     LEFT join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
     LEFT join #tbl_designation D on D.Designation_ID=M.Designation_ID
     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

    If you want to select SAL_ID column with value 'NULL', then you could select the value 'NULL' for SAL_ID.

    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',null [Sal_ID]
    FROM #tbl_Employee_Master M
     LEFT join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
     LEFT join #tbl_designation D on D.Designation_ID=M.Designation_ID
     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

     

    Best regards,

    Sean
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 1, 2020 8:25 AM

All replies

  • User-1330468790 posted

    Hi akhterr,

     

    I can see that the currently result contains all employees excluding those people who have records with not-null "sal_ID".

    If this is not what you want, I guess the only result you desired might be:

    • Display the employees who have records in the table "#tbl_assign_Allowance_Deduction" with "null" value of "sal_ID"

     

    Then you could modify the query using default "JOIN"(INNER) as below:

    Drop table if exists  #tbl_designation  
    Drop table if exists  #tbl_Type_allowance_Deduction  
    Drop table if exists  #tbl_assign_Allowance_Deduction  
    Drop table if exists  #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
      join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
      join #tbl_designation D on D.Designation_ID=M.Designation_ID
      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

    Result: (only containing the record for Akhter)

     

    If I misunderstand your purpose, feel free to let me know.

    Best regards,

    Sean

    Monday, August 31, 2020 3:06 AM
  • User-367318540 posted

    No,i want to display all employee ,from employee master table,whether sal_id exit or not ,means that emp_ID exit or not in tbl_assign_Allowance_Deduction table,must fetch all employee

    Monday, August 31, 2020 10:06 AM
  • User-1330468790 posted

    Hi akhterr,

     

    A simpler modification on your origin codes is to remove the "WHERE" condition and use "LEFT JOIN" with a correct order.

     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
     LEFT join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
     LEFT join #tbl_designation D on D.Designation_ID=M.Designation_ID
     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

    Result:

     

    Best regards,

    Sean

    Monday, August 31, 2020 11:19 AM
  • User-367318540 posted

    i do  not want SAL_ID ,it must be null,

    Monday, August 31, 2020 11:40 AM
  • User-1330468790 posted

    Hi akhterr,

     

    If you don't need SAL_ID, why do you add this column in the select query?

    Just remove "SAL_ID" in SELECT part:

    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'  -- remove the sal_id here
    FROM #tbl_Employee_Master M
     LEFT join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
     LEFT join #tbl_designation D on D.Designation_ID=M.Designation_ID
     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

    If you want to select SAL_ID column with value 'NULL', then you could select the value 'NULL' for SAL_ID.

    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',null [Sal_ID]
    FROM #tbl_Employee_Master M
     LEFT join #tbl_assign_Allowance_Deduction ASD on asd.Emp_ID=M.Emp_ID
     LEFT join #tbl_designation D on D.Designation_ID=M.Designation_ID
     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

     

    Best regards,

    Sean
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 1, 2020 8:25 AM