473,326 Members | 2,134 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT employee_number
  2. FROM payslip_balances
  3. WHERE employee_number IN (SELECT DISTINCT employee_number
  4.                          FROM payslip_balances xpbt
  5.                          WHERE xpbt.balance_name ='Gross Pay' AND xpbt.balance_value = 0)
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT employee_number
  2. FROM payslip_balances
  3. WHERE employee_number IN (SELECT DISTINCT employee_number
  4.                          FROM payslip_balances xpbt
  5.                          WHERE xpbt.balance_name ='Total Pay' AND xpbt.balance_value = 0)
Any help would be greatly appreciated.

Many Thanks

Martin
Oct 2 '07 #1
1 1190
debasisdas
8,127 Expert 4TB
You just need to concadinate both the statments using AND.
Oct 2 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: ScottH | last post by:
I was looking for thw SQL to delete dupes from a table, and came across this. All who saw it agreed in principle, but I can't quite figure out the logic. If we are deleting all rows whose rowid...
4
by: francis70 | last post by:
Hi, I have these 2 problem? Is there a way in Oracle to read UNCOMMITED data. i.e. in Oracle the normal behaviour is that a user's updates to a table are visible to other users ONLY when the...
0
by: JWM | last post by:
I am trying to implement Oracle connection pooling for the following code, which was written by someone else. Here is my main question -- this java file creates code that is executed every hour,...
133
by: jonathan | last post by:
hey all, I realize that this question might pop up from time to time, but I haven't seen it a while and things might of changed, so - Right now (July 2004) how does mysql stand up in...
36
by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
8
by: Jan van Veldhuizen | last post by:
The UPDATE table FROM syntax is not supported by Oracle. I am looking for a syntax that is understood by both Oracle and SqlServer. Example: Table1: id name city ...
27
by: Nasir | last post by:
Hi there, Is it posible to set the collation of SQL server 2005 like Oracle, which is that objects names and column names are case-insensitive, but data is sensitive;e.g: In Oracle: --create...
24
by: Frank Swarbrick | last post by:
We have a batch process that inserts large numbers (100,000 - 1,000,000) of records into a database each day. (DL/I database.) We're considering converting it to a DB2 table. Currently we have...
11
by: lenygold via DBMonster.com | last post by:
Hi everybody! This query is supposed to count consecutive years from the current year without OLAP. Input Table: ID DateCol 1 02/01/2006 1 01/01/2006 1 01/01/2005
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.