Display all employee

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

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

User-367318540 posted

i do  not want SAL_ID ,it must be null,

Answer 5

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