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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

You can do

SELECT DISTINCT site 
FROM table_name 
WHERE site NOT IN (SELECT DISTINCT site 
                   FROM table_name 
                   WHERE event = 'A3')

CodePudding user response：

The sub-query will return Sites with A3 event. Hence joining the table using LEFT JOIN and filtering for Sites not available in sub-query

SELECT DISTINCT Site 
FROM your_table as tbl
LEFT JOIN
(
  SELECT DISTINCT Site
  FROM your_table
  WHERE Event = 'A3'
) as sq
ON tbl.site=sq.site
WHERE sq.Site IS NULL

CodePudding user response：

SELECT Well  
FROM test
where Well not in (select Well from tablename where Evt = 'A3');