I have Table with Columns Name, EmailId, Process, Status Like Below. Now what i would like to have is the emailId and Status of people whoes Status are closed for all the Process – Process1, process2, process 3

Input
 

Output

Query1

  SELECT  fruit_email_id
    FROM  Fruits AS t
   GROUP BY fruit_email_id
  HAVING COUNT( CASE WHEN Status  'Closed' 
                     THEN 1                       
                      END) = 0;

Query2

   SELECT  t.fruit_email_id
     FROM  (SELECT DISTINCT fruit_email_id
              FROM Fruits) AS t
              LEFT JOIN Fruits AS tt ON  tt.fruit_email_id = t.fruit_email_id AND 
                   tt.Status  'Closed'
    WHERE tt.fruit_email_id IS NULL;
Posted in SQL.

Leave a reply