I want to create a SQL query to identify wells which do not have A3 events. I am using SQL server.
I have tried multiple ways like checking count, comparing with event A3 but still unable to get what i want.
From below example desired result would be W3 and W4
| Site | Event |
|---|---|
| W1 | A1 |
| W1 | A2 |
| W1 | A3 |
| W2 | A1 |
| W2 | A2 |
| W2 | A3 |
| W3 | A1 |
| W3 | A4 |
| W4 | A2 |
| W4 | A4 |
>Solution :
NOT EXISTS is a very performant approach
Example
Select *
From YourTable A
Where not exists ( select 1
from YourTable T
where T.Well=A.Well and T.[Event]='A3'
)
Results
Well Event
W3 A1
W3 A4
W4 A2
W4 A4