Deleting from an Oracle table when a Where Clause needs to take values from 2 rows

[martinley]

Hi,

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.

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)

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)

Any help would be greatly appreciated.

Many Thanks

Martin

[debasisdas]

You just need to concadinate both the statments using AND.