Select Statement - Subject to

Question

Take a look at the table below:

Status ----------------- Email Approved [e-mail] Canceled --------------- [e-mail] Canceled --------------- [e-mail] Canceled --------------- [e-mail] Canceled --------------- [e-mail] 

I fulfill the request

 "SELECT DISTINCT Status, Email FROM dbo.sales": Status ----------------- Email Approved --------------- [e-mail] Canceled [e-mail] Canceled --------------- [e-mail] 

I would like to configure the query to select lines that have the status "Canceled" ONLY WHEN there is no line with the same email address with the status "Approved".

In other words, in this example, I would select only the last entry (Canceled - [e-mail] ).

Is it possible? Thanks in advance.

Answer 1

 Select distinct status,email From dbo.sales Where email Not In ( select email from dbo.sales Where status='Approved' ) 

Answer 2

try it

 Select Status, Email FROM dbo.sales WHERE Status = 'Canceled' AND NOT EXIST(Select Status, Email FROM dbo.sales Where Status='Approved') GROUP BY Status, Email 

Answer 3

Assuming there are only "Approved" and "Canceled" in the Status columns:

 select status, email from dbo.sales where email not in (select email from dbo.sales where status = 'Approved') group by email 

Answer 4

You can use the following queries:

Method 1:

 SELECT DISTINCT t.status, t.email FROM dbo.test t LEFT JOIN (SELECT * FROM test WHERE STATUS = 'Approved')z ON z.email = t.email WHERE t.STATUS = 'Canceled' AND z.STATUS IS NULL 

Method 2:

 SELECT DISTINCT status, email FROM dbo.sales t WHERE t.STATUS = 'Canceled' and t.email Not In ( SELECT email FROM dbo.sales WHERE status='Approved' ) 

Answer 5

If this applies to canceled emails, you can try EXCEPT:

 SELECT Email FROM dbo.sales WHERE Status = 'Canceled' EXCEPT SELECT Email FROM dbo.sales WHERE Status = 'Approved' ; 

or, if Status should be included in the output:

 SELECT Status, Email FROM dbo.sales WHERE Status = 'Canceled' EXCEPT SELECT 'Canceled', Email FROM dbo.sales WHERE Status = 'Approved' ; 

If you were actually intended to display emails that were always approved or always canceled, one way would be to repeat the above logic for each subset:

 ( SELECT Status, Email FROM dbo.sales WHERE Status = 'Canceled' EXCEPT SELECT 'Canceled', Email FROM dbo.sales WHERE Status = 'Approved' ) UNION ALL ( SELECT Status, Email FROM dbo.sales WHERE Status = 'Approved' EXCEPT SELECT 'Approved', Email FROM dbo.sales WHERE Status = 'Canceled' ) ; 

Alternatively, however, you can try grouping and counting different Status values ​​in groups:

 SELECT MIN(Status) AS Status, Email FROM dbo.sales GROUP BY Email HAVING COUNT(DISTINCT Status) = 1 ; 

This other method will work for any number of possible Status es.