SQL – select based on value existence in another column

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

Answer

Aggregation is one approach:

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);