| S.no | emp_id | emp_name | Dept |
|---|---|---|---|
| 1 | 100 | John | Sales |
| 2 | 100 | John | Accounts |
| 3 | 200 | Mike | Sales |
| 4 | 300 | Mark | Sales |
| 5 | 300 | Mark | Accounts |
| 6 | 400 | Tom | Sales |
I need to pull all the emp_id who are linked ONLY to Sales Dept and ignore the ones that are in both Sales and Accounts. I am using DB2 z/os. Any suggestions would be helpful? Thanks in advance.
CodePudding user response：
An anti-join will produce the result you want.
For example:
select s.*
from employee s
left join employee a on a.emp_id = s.emp_id and a.dept = 'Accounts'
where s.dept = 'Sales' and a.emp_id is null
For good performance you can try adding the index:
create index ix1 on employee (emp_id, dept);