How do I do NOT CONTAINS?

I’m having difficulties with a question on an assignment and writing the correct query. I’ve been at it for days, and it’s due soon. I’m trying to show all project numbers that do not contain employee id’s of 114 and 113 (the application designers). Looking at the table data, I can see without writing code that the project numbers are 15 and 18, but I can’t get that output. This code simply outputs everything except 114 and 113 employees.

This is the question:

Which Project does not utilise an Application Designer?

This is my code:

> proc sql;
>         select distinct proj_num708, emp_num708
>     from asmnt708.assignment708
>     where emp_num708
>      not in (select distinct emp_num708 from asmnt708.assignment708 where emp_num708 in (114, 113));

This is my codes output:

This is my table data:

Preferred output: My preferred output is just the project numbers 15 and 18

Answer

You can use not exists

select 
    proj_num708, 
    emp_num708
from asmnt708.assignment708 a
where not exists (
    select distinct emp_num708 
    from asmnt708.assignment708 b
    where a.proj_num708 = b.proj_num708
    and emp_num708 in (114, 113)
);

Advertisement

Answer