mysql - How to select record except those that match the join? -

in our webapps, employee can perform 1 or more jobs @ same time, database structured this:

http://rextester.com/vtfnd62465

table tma_employee:

emp_id | emp_name -------|--------- 1      | bob 2      | max 3      | sally 

table tma_job:

job_id | job_name -------|--------- 1      | dev 2      | sales 3      | mgmt 

relation table between employees , jobs tma_employee_job:

emp_id | job_id -------|------- 1      | 1 1      | 2 2      | 2 2      | 3 3      | 3 3      | 1 

given structure, easy select employees perform 1 or more specific jobs:

select distinct e.* tma_employee e join tma_employee_job ej using (emp_id) join tma_job j using (job_id) j.job_name = 'dev'; 

however, how can select employees not perform job?

i tried inverting condition this:

select distinct e.* tma_employee e join tma_employee_job ej using (emp_id) join tma_job j using (job_id) j.job_name != 'dev'; 

however not work because, example, bob on sales job, different dev, employee bob found above query.

i have found works:

select distinct e.* tma_employee e e.emp_id not in (     select ej.emp_id     tma_employee_job ej     join tma_job j using (job_id)     j.job_name = 'dev' ); 

however, requires me use subquery, avoid, because main query built function which, based on parameters receives, starts select e.* tma_employee e , adds various joins , clauses.

is possible obtain same result of query uses sub-query, while sticking simple joins?

sql demo without subquery:

select e.emp_id, e.emp_name tma_employee e left join tma_employee_job ej using (emp_id) left join tma_job j on j.job_id = ej.job_id group e.emp_id, e.emp_name having count(case when job_name = 'dev' 1 end) = 0;