CODEWITHSUNDEEP
sqloracle-databaseoracle10g
bipin
bipin

Reputation: 19

How to get distinct employees that do not have a particular skillset

I have a table that has two columns. Employee_id (which is unique per employee) and next column for employee skillset. One employee can have multiple skillset. How do I retrieve the list of distinct employees who don't have skillset 'c' if A,B,C,D,E are the five types of skillset that employees can have. 

employee_id skillset
1           A
1           C
2           E
3           A
3           B
3           C
4           D
4           C
5           B

I have tried self join and other methods but it is not working. 

select distinct employee_id from employee_skillset where skillset not like 'C'

When I run my query, it is still giving me employee_ids that have skillset of "c"

Upvotes: 0

Views: 237

Answers (3)

Littlefoot
Littlefoot

Reputation: 142968

MINUS set operator is one option:

SQL> with employee_skillset (employee_id, skillset) as
  2    (select 1, 'a' from dual union all
  3     select 1, 'c' from dual union all
  4     select 2, 'e' from dual union all
  5     select 3, 'a' from dual union all
  6     select 3, 'b' from dual union all
  7     select 3, 'c' from dual union all
  8     select 4, 'd' from dual union all
  9     select 4, 'c' from dual union all
 10     select 5, 'b' from dual
 11    )
 12  select employee_id from employee_skillset
 13  minus
 14  select employee_id from employee_skillset where skillset = 'c';

EMPLOYEE_ID
-----------
          2
          5

SQL>

Yet another option:

 <snip>
 12  select employee_id
 13  from (select employee_id,
 14               case when skillset = 'c' then 1 else 0 end flag
 15        from employee_skillset
 16       )
 17  group by employee_id
 18  having sum(flag) = 0;

EMPLOYEE_ID
-----------
          2
          5

SQL>

Or:

 <snip>
 12  select employee_id
 13  from (select employee_id,
 14               listagg(skillset, ',') within group (order by null) lagg
 15        from employee_skillset
 16        group by employee_id
 17       )
 18  where instr(lagg, 'c') = 0;

EMPLOYEE_ID
-----------
          2
          5

SQL>

Upvotes: 0

MonkeyMonkey
MonkeyMonkey

Reputation: 160

What are your expected results from your data set? 2 and 5?

Why not something like below

SELECT DISTINCT employee_id
FROM Table1
WHERE skillset <> 'C';

Upvotes: 0

forpas
forpas

Reputation: 164139

You can group by employee_id and set a condition in the HAVING clause:

select employee_id 
from employee_skillset 
group by employee_id
having sum(case when skillset = 'C' then 1 else 0 end) = 0

Or with NOT EXISTS:

select distinct s.employee_id 
from employee_skillset s
where not exists (
  select 1 from employee_skillset
  where employee_id = s.employee_id and skillset = 'C'
)

Upvotes: 1

Related Questions