CODEWITHSUNDEEP
sql
Luca5om3
Luca5om3

Reputation: 71

Join two tables to create a third one sql

I have two tables and I want to create a third that join the other two. My purpose is creating a CRUD app to switch employees over departments

Table Employees:

| id | Name | Other |
|----|------|-------|
| 1  | John | x     |
| 2  | Jack | y     |
| 3  | Phil | z     |

Table Departments:

| id | Name| Zone |
|----|-----|------|
| 1  | WH1 | a    |
| 2  | WH2 | b    |
| 3  | WH3 | c    |

I want create a third table named Situation that has Employee name and Department name and zone.

How to do that? And, is that the best practice? Every employee switch frequently over departments.

Thanks

Upvotes: 0

Views: 446

Answers (4)

Jani
Jani

Reputation: 406

Since each of your Employees has only one Department, the best practice would be to add an additional column to your Employees table, which will reference the Department of the given Employee.

Table Employees:

| id | Name | Other | Department_Id
|----|------|-------|--------------
| 1  | John | x     | 1
| 2  | Jack | y     | 1
| 3  | Phil | z     | 2

Table Departments:

| id | Name| Zone |
|----|-----|------|
| 1  | WH1 | a    |
| 2  | WH2 | b    |
| 3  | WH3 | c    |

Now you do a JOIN, for example:

SELECT e.id, 
       e.Name, 
       d.Name AS Department_Name, 
       d.zone AS Department_Zone
FROM Employees e JOIN Departments d 
       ON e.Department_Id = d.id

Then you'll see your employees and their department in the same line:

| id | Name | Department_Name | Department_Zone |
|----|------|-----------------|-----------------|
| 1  | John | WH1             | a               |
| 2  | Jack | WH1             | a               |
| 3  | Phil | WH3             | c               |

To save the result of this select, you can use SELECT INTO:

SELECT e.id, 
       e.Name, 
       d.Name AS Department_Name, 
       d.zone AS Department_Zone
INTO employee_departments
FROM Employees e JOIN Departments d 
       ON e.Department_Id = d.id

This will save all the newly created information into the employee_departments table.

Upvotes: 1

Steven King
Steven King

Reputation: 45

There would need to be a relationship between the Employees table and the Departments table. Ideally, there should be a dept_id column within the employees table (foreign key). This is necessary to relate an employee to a department so that you can populate a table called: SITUATION. If you don't need the data stored, you can simply create a View. If there is a dept_id within the Employees table then writing your SQL can be as such:

SELECT E.NAME AS EMPLOYEE_NAME,
       D.NAME AS DEPARTMENT_NAME
FROM   EMPLOYEES E,
       DEPARTMENTS D
WHERE  E.DEPT_ID = D.DEPT_ID;

I hope this is helpful.

Upvotes: 0

Daniel Marcus
Daniel Marcus

Reputation: 2686

Following up on my comment above:

First create cross ref table

create table employee_dept_cross_ref (employee_id int, department_id int, startdate datetime, enddate datetime)

When an employee changes departments, populate enddate for any record with that employeeid with enddate=null and insert the new mapping 

update employee_dept_cross_ref set enddate=getdate() where employeeid=@employee_id and departmentid<>@department_id and enddate is null
insert employee_dept_cross_ref
values
(@employee_id, @department_id, getdate(), null)

Then point your app to pull mapping where enddate is null

Upvotes: 1

Mauri
Mauri

Reputation: 247

You could have a Table saving the Employee Id and the Department Id ( in witch the Employee is working right now ). Then you could create a select joining to the Employee and the Department retrieving the extra information ( like employee name or Department zone ). This is the most common scenario. If you have performance issue on this ( I m sure that no ), you could include tuning tasks ( like name duplication, zone duplication, but this is most unperformed on the EmployeeDepartment updates ).

Upvotes: 0

Related Questions