Department X person is controlled by Department Y person

I have 3 tables Employee, Project and Workson, Sample data is:

Employee:

SocialSecurityNo   Department_No

121212              1
456789              2
666666              2
444444              2

Workson

ESSn              Projectno

121212             5000
456789             1000
456789             2000
666666             1000
666666             2000
666666             3000
666666             4000
666666             5000
666666             6000

Project:

ProjectNumber  Dnum

1000           5
2000           5
3000           5
4000           4
5000           1
6000           4

I have to check that Employee with SocialSecurityNo x is selected for Dno y but he is working on a project assigned for department z.

I have written a query for it, My query is working finding all the SSn which are working are selected for department x and working for department x, I am trying to do the opposite, but when I apply NOT IN on a subquery then it also give me those social security numbers which have no data in the workson table

Below is my query:

Select E.SocialSecurityNo FROM EMPLOYEE E WHERE E.SocialSecurityNo NOT IN 

(Select E.SocialSecurityNo From 

EMPLOYEE E Join WORKSON W ON E.SocialSecurityNo=W.ESSn 

Join PROJECT P ON E.Department_No=P.Dnum 

Where W.ProjectNumber=P.Projectno);

Solution

Is this what you want?

select e.*, p.*
from employee e
inner join workson w on w.essn = e.socialsecurityno
inner join project p on p.projectnumber = w.projectno
where p.dnum <> e.department_no

This brings all employee/project pairs where the department of the employee does not match that of the project.

Side note: I would recommend adjusting the names of the columns across the tables, so the schema is easier to understand. There is no reason why a social security number would be called essn in one table and socialsecurityno in another; same goes for projectnumber vs projectno and dnum vs department_no.