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 |
Advertisement
Answer
Aggregation is one approach:
x
SELECT Site
FROM yourTable
GROUP BY Site
HAVING COUNT(CASE WHEN Event = 'A3' THEN 1 END) = 0;
If you really also need to view all sites for matching wells, then put the above into a CTE and use it as a subquery:
WITH cte AS (
SELECT Site
FROM yourTable
GROUP BY Site
HAVING COUNT(CASE WHEN Event = 'A3' THEN 1 END) = 0
)
SELECT Site, Event
FROM yourTable
WHERE Site IN (SELECT Site FROM cte);