[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-05
3
Medium Priority
?
407 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Michael Fowler
  • 2
3 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36921353
On mobile right now and might miss a copy/paste but it should go something like:


Select EMPLOYEE_ID,SURNAME,FIRST_NAME from MSF810 m81 where not exists ( select 1 from MSF872 m87 where AUTHTY_TYPE in ('WOAU','WORI')
And m81.employee-id=m87.position_id)
0
 
LVL 23

Author Comment

by:Michael Fowler
ID: 36921393
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
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36921698
Glad to help even if from a tiny keyboard.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question