I have a table 1 "Grading" with columns [GID,gName], table 2 "Gradeentitlements" with columns [GID,entType], table 3 "entitlementTypes" with columns[entType,eName,taxable,default,accno,grouping] and finally table 4 "employeebenefits" with columns [personnalno,entType,amount]
employees in the same grade is entitle to the entitlements but is not neccessarily given to every individual in that grade.
I want to select personalno,taxable,default,amount and if the employee is entitle based on grade but has null value to return zero in the 'amount' column.
this is my sql statement select e.name,eb.amount,e.[default] from EntitlementTypes e inner join EmpBenefits eb on e.entType=eb.entType right outer join GradeEntitlements g on g.entType=eb.entType where EmpNo='0011' and g.GID='002' union select e.name,eb.Amount,e.[default] from EntitlementTypes e,EmpBenefits eb right outer join GradeEntitlements g on g.entType=eb.entType where EmpNo='0011' and g.GID='002' and g.entType not in(select entType from GradeEntitlements where entType='001') but it doesn't display all the gradeentitlements with null values
have tried this other one select e.name,eb.amount,e.[default] from EntitlementTypes e inner join EmpBenefits eb on e.entType=eb.entType right outer join GradeEntitlements g on g.entType=eb.entType where EmpNo='0011' and g.GID='002'
but it displays the value of amount to equal even those with null