Join 3 tables with where clause without common table

Question

I have 3 tables employee, sales, department

Employee table

------------------------------
  AssignemtID | EmployeeEmail
-------------------------------
      1       | abc.@cam.com
-------------------------------
      2       | bdc@cam.com
-----------------------------
      3       | dgs@cam.com
-----------------------------
      4       | tyr@cam.com
------------------------------

Sales table

Sales | EmployeeEmail     | Emp ID
------------------------------------
24333  | abc.@cam.com  |  34
------------------------------------
46373 | bdc@cam.com    | 24
-----------------------------------
32212 | dgs@cam.com    | 78
-----------------------------------

Department table

Department | Sales       |Emp ID
-------------------------------
AS         | 24333       |34
-------------------------------
we         | 46373       |24
-----------------------------
de         | 32212       |78
------------------------------

I want to see all the employee who are there in the Employee table but not in the Department table. the data is huge in all the 3 tables

These tables don't make any sense. Why does the `Sales` table have emails, but no information about sales? — Barmar, Nov 30 '20 at 06:25
employee table and sales can be connected on Employeeemail. Sales and Department table on Sales figure, but cannot connect employee table to department table as there is no common column — Sandy, Nov 30 '20 at 06:27
I think all three tables mis-organized. 1-Employee Table didn't have an Id,2- You have to use string to join Employee table and Sales table. 3- Department table has two different Id to join etc. — yusuf hayırsever, Nov 30 '20 at 06:49

score 0 · Answer 1 · answered Nov 30 '20 at 06:33

You can connect Employee to Department by joining it with Sales.

SELECT *
FROM Employee
WHERE EmployeeEmail NOT IN (
    SELECT EmployeeEmail
    FROM Sales AS s
    JOIN Department AS d ON s.Sales = d.Sales
)

or

SELECT e.*
FROM Employee AS e
LEFT JOIN Sales AS s ON s.EmployeeEmail = e.EmployeeEmail
LEFT JOIN Department AS d ON s.Sales = d.Sales
WHERE d.Department IS NULL

Join 3 tables with where clause without common table

1 Answers1