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;