Reporting values that do not exist in SQL 1

[klornpallier]

Probably a easy question but I would be greatful for some help. I have the table below and I need to report on just the EMP_NO's that do not have ELEMENT's BUSMILES & PRIVATMIL. Any clues on this?

EMP_NO CAR_TYPE PER_NO ELEMENT
---------------------------------------------------
00111 COMPANY 5 S&SWGR
00111 COMPANY 5 BHSF
00111 COMPANY 5 SALARY
00136 COMPANY 6 SALARY
00136 COMPANY 6 S&SWOR
00136 COMPANY 6 BUPA
00136 COMPANY 5 BUSMILES
00136 COMPANY 5 SALARY
00136 COMPANY 5 PRIVATEMIL

 

[Dagon]

Would it not just be something like this ?

select distinct empno from table
where element not in ('BUSMILES', 'PRVATEMIL');

 

[klornpallier]

Thanks but I tried that one! For example I would like EMP_NO 00136 not to be displayed in the results because it has ELEMENTS BUSMILES & PRIVATMIL. This method still shows 00136 because of the other ELEMENTS. Any ideas?

 

[Dagon]

select * from table t1
where element not in ('BUSMILES', 'PRVATEMIL')
and not exists
(select 1 from table t2
where t1.emp_no = t2.emp_no
and element in ('BUSMILES', 'PRVATEMIL'))

 

[klornpallier]

Thats great and works a treat. Thanks your a star!