Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading