MYSQL basic conditional aggregation for same Columns

Question

i have the following issue and i don't know how can i achieve that.

I need to filter based on both conditions + same property

Get Users where the event is "paymentFailed" and event is not "paymentSuccess"

• Basically i need to filter all "Failed Payments" for every user, but the condition implies that a paymentSuccess must not exist for the same user.

• If the paymentSuccess exists for the user, the result should be empty.

id  name    event             transaction_number    
2   John    paymentFailed         002   
3   John    paymentInProcess      003   
4   John    paymentStucked        004   
5   John    paymentSuccess        005   
6   Jane    paymentFailed         006   
7   Jane    paymentSuccess        007   
8   Reese   paymentFailed         008   
9   Reese   paymentFailed         009   
10  Reese   otherPayment          010   

Something like this (when paymentSuccess does not exists)

SELECT * FROM USERS where event != "paymentSuccess" AND event = "paymentFailed"

Result:

id  name    event             transaction_number    
8   Reese   paymentFailed         008   

And (when paymentSuccess exists)

SELECT * FROM USERS where event = "paymentSuccess" AND event = "paymentFailed"

Result:

id  name    event             transaction_number    
------------------------ Empty table -----------------------

Answer 1

Use NOT EXISTS:

SELECT u1.* 
FROM USERS u1 
WHERE u1.event = 'paymentFailed'
  AND NOT EXISTS (SELECT 1 FROM USERS u2 WHERE u2.name = u1.name AND u2.event = 'paymentSuccess')