CODEWITHSUNDEEP
mysql
Edward
Edward

Reputation: 13

MySql exclusion query

I have a table that looks like this

JOB_ID  | Email        | Type       | Date      
_____________________________________
28319   | [email protected] | Service    | 11-24-2016
_____________________________________ 
28412   | [email protected] | Rotation   | 11-24-2016
_____________________________________
38123   | [email protected] | Service    | 11-24-2016
_____________________________________
28199   | [email protected]| Service    | 11-24-2016

What would be the query i would run to return those who have a service scheduled but do not have a rotation on a specific date.

This is my attempt:

SELECT j.Job_ID,j.Email,j.Type,j.Date
from Jobs j 
join
     Jobs  j2
     on j.Email = j2.Customer_Email
where j2.JOB_ID NOT IN (select j.JOB_ID
                  from Jobs j
                  where j.Type = 'Rotation'
                 )     AND j.Date = '11-24-2016';

So my query would return andy and roger.

Upvotes: 1

Views: 31

Answers (1)

Reto
Reto

Reputation: 1343

This should do the trick for you, no need for joins here:

SELECT * FROM Jobs WHERE Date = '11-24-2016' AND Type = 'Service' AND 
Email NOT IN (SELECT Email FROM Jobs WHERE Date = '11-24-2016' AND 
Type = 'Rotation');

Upvotes: 1

Related Questions