Link to home
Start Free TrialLog in
Avatar of Michael Fowler
Michael FowlerFlag for Australia

asked on

SQL Help - Find Employee_id in one table where it does not exist in a second table with a specified authy_type

I have two tables MSF810 and MSF872.
MSF810 stores emploee details such as EMPLOYEE_ID,SURNAME,FIRST_NAME etc
MSF872 stores the authorities each employee has on the system. Each employee has one entry in MSF872 for each authority they have and so each employee may have up to 12 entries in the MSF872

MSF810.EMPLOYEE_ID is equal to MSF872.POSITION_ID

I am trying to get EMPLOYEE_ID,SURNAME,FIRST_NAME from MSF810 where there is no entry in MSF872 for that employee with the AUTHTY_TYPE = 'WOAU' or AUTHTY_TYPE = 'WORI' in MSF872

Michael
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Michael Fowler
Michael Fowler
Flag of Australia image

ASKER

Great work for a solution from your mobile. Couple of small changes to allow for the copy paste and it work.

thank you

here is the completed solution

Select EMPLOYEE_ID,SURNAME,FIRST_NAME
from MSF810 where not exists (
    select POSITION_ID  
    from MSF872
    where AUTHTY_TYPE in ('WOAU','WORI') And MSF810 .EMPLOYEE_ID=MSF872 .POSITION_ID)

Michael
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Glad to help even if from a tiny keyboard.