Query Question

[alwayshouston]

Hi All
I have a table as below:

Dept_ID Salary Original Modified
ACCT 5000 Y N
ACCT 5000 N Y
MKTG 7500 Y N
MKTG 8000 N Y

I need to get the result from the table in which if there is a duplication (ACCT), I would need Original =Y and Modified = N. If there is no duplication (MKTG), I would need the record where Modified = Y.
My output should be as follow:

Dept_ID Salary Original Modified
ACCT 5000 Y N
MKTG 8000 N Y

How can I accomplish this logic in a query/queries? Please note that I cannot alter the table design because it comes from the system this way.

Thanks in Advance!!!

 

[BNPMike]

At a logical level you need to count original (for example) grouped by Dept_id + salary. Then you can select records depending a combination of Original, Modified and Count.

Most likely you can do this with a select with a subquery in it.

 

[alwayshouston]

Thanks BNPMike!
I am still confused. How can you do this in Sub Query? Do you have any example of SQL for this?

 

[BNPMike]

Here's an example of subqueries:

SELECT [Hotel Name],
Rooms
FROM [Rutland ex]
WHERE ( Rooms >10) AND Town in (

SELECT Town from
(SELECT Count(ID) AS CountOfID,
Town
FROM [Rutland ex]
GROUP BY Town
HAVING (((Count(ID))>3))
)
)
;

Here the inner query finds the towns with more than 3 hotels. The next outer query selects the name of those towns. The main query then selects hotels which have more than 10 rooms and are in one of the towns found in the subqueries.

You can do the same by having the inner query identify those 'things' that have two rows, and then create some where clauses that mix Original, Modified values with membership or not of the multiples table.