Ms_YMG Ms_YMG - 5 months ago 11
SQL Question

Remove duplicate columns from Query result

Please help me!! Newby with Sql queries

Select *
from(
select EmpID,
sum(IncomeTax) as TaxAmount,
sum(bsalary) as SalaryAmount
from PayrollHistory Pay
group by EmpID
) cumSalary
Right JOIN (
Select PayrollHistory.EmpID,
(select firstName +' '+coalesce(middleInitial,' ')+' '+ lastName
from Employee
where Employee.EmpID=PayrollHistory.EmpID)as name,
PayrollHistory.IncomeTax,
(PayrollHistory.bsalary+sum(ISNULL(Allw.amount,0)))totalTaxableSUM
from PayrollHistory
left join (
select *
from AllowanceHistory
where AllowanceHistory.taxStatus=1
) as Allw
on Allw.EmpID=PayrollHistory.EmpID and Allw.payMonth=PayrollHistory.payMonth
where PayrollHistory.payMonth=3
group by PayrollHistory.EmpID, PayrollHistory.IncomeTax, PayrollHistory.bsalary
) as tbl
on tbl.EmpID =cumSalary.EmpID


The above query result gives 2 EmpID rows that are the same. How can remove one of them and still get the same result

Answer

Use column name selection instead of using * , refer as below

Select cumSalary.*,PayrollHistory.name , **....etc** from(
select EmpID,  sum(IncomeTax) as TaxAmount,sum(bsalary) as SalaryAmount from     
PayrollHistory   Pay group by EmpID
) cumSalary
Right JOIN (
Select PayrollHistory.EmpID,(select firstName +'  '+coalesce(middleInitial,'   
')+' '+ lastName from Employee where   
 Employee.EmpID=PayrollHistory.EmpID)as   name,
  PayrollHistory.IncomeTax,(         PayrollHistory.bsalary+sum(ISNULL(Allw.amount,0)  
))totalTaxableSUM
from PayrollHistory  
left join (select * from AllowanceHistory where AllowanceHistory.taxStatus=1  
) as Allw on 
Allw.EmpID=PayrollHistory.EmpID and Allw.payMonth=PayrollHistory.payMonth  
where PayrollHistory.payMonth=3
group by   
PayrollHistory.EmpID,PayrollHistory.IncomeTax,PayrollHistory.bsalary
) as tbl on tbl.EmpID =cumSalary.EmpID