sql query to get data from an audit table where column match one value, but not another-CodePudding

I have a table t_audit with 4 columns: name, address, updated (Y or N), and user_id (ID of the user who updates it).

I want to search the audit table, to get the names of customers updated by user_id 1, but not 2.

I tried this:

select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 1 ) 
    and not exists (ta.updated='Y' and user_id = 2)

But it does not work. How can I fix this?

CodePudding user response：

for Oracle

select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 1 ) 
minus
select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 2 )

for others

select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 1 ) 
EXCEPT
select ta.name 
from t_audit ta 
where (ta.updated = 'Y' and user_id = 2 )

CodePudding user response：

Your not-exists syntax is incorrect, the following is untested of course by should be close to what you are expecting

select ta.name 
from t_audit ta 
where ta.updated = 'Y' and ta.user_id = 1  
  and not exists (
    select * from t_audit ta2
    where ta2.name=ta.name and ta2.updated='Y' and ta2.user_id = 2
)