I have a table that contains data similar to the below, apologies for the table layout:
employee_number balance_name balance_value
a1 Gross Pay 100
a1 Total Deduction 50
a1 Total Pay 50
b2 Gross Pay 50
b2 Total Deduction 50
b2 Total Pay 0
c1 Gross Pay 0
c1 Total Deduction 0
c1 Total Pay 0
I would like to delete any employee in the table that has Gross Pay and Total pay equal to 0.
(c1 in the above example, b2 would need to stay because even though his Total pay is 0 he is still paid Gross)
I can return everyone that has a Gross Pay of 0 and everyone that has a Total Pay of 0 but I am struggling to
work out how to return everybody that has both a Total Pay and Gross Pay of 0.
Expand|Select|Wrap|Line Numbers
- SELECT DISTINCT employee_number
- FROM payslip_balances
- WHERE employee_number IN (SELECT DISTINCT employee_number
- FROM payslip_balances xpbt
- WHERE xpbt.balance_name ='Gross Pay' AND xpbt.balance_value = 0)
Expand|Select|Wrap|Line Numbers
- SELECT DISTINCT employee_number
- FROM payslip_balances
- WHERE employee_number IN (SELECT DISTINCT employee_number
- FROM payslip_balances xpbt
- WHERE xpbt.balance_name ='Total Pay' AND xpbt.balance_value = 0)
Many Thanks
Martin