CODEWITHSUNDEEP
sql
r4sn4
r4sn4

Reputation: 117

Join query to select fields from 2 tables

I created 3 tables viz Employee, Department and EmpDept.

1) Employee Table fields are

:- EmployeeID(Primary Key) , Fname, Lname, Age, Salary, Address

2) Department Table fields are :-

DepartmentID(Primary Key), DeptName , DeptLocation

3)EmpDept table fields are :-

ID(Primary Key), EmpId(Foriegn Key reference Employee table) , DeptID(Foreign key references Department table)

I want to perform following operations :-

a) Select all the fields from table Employee and Department
b) Deleting a particular department also deletes all the employees from Employee table belonging to that particular department.

I am not getting what fields to include in 3rd table(i.e EmpDept) and how to apply join to select columns

My attempt

create procedure EmpDept2 
as 
Begin 
select Employees.EmployeeID
      , Employees.FirstName
      , Employees.LastName
      , Employees.Age
      , Employees.CreatedDate
      , Employees.LastModifiedDate
      , Employees.ModifiedBy
      , Employees.Active
      ,Department.DepartmentID
      , Department.DeptName 
from Employees, Department inner join EmpDept 
on EmpDept.EmpId = Employees.EmployeeID 
on EmpDept.DeptId= Department.DepartmentID

Upvotes: 0

Views: 1536

Answers (1)

M.Ali
M.Ali

Reputation: 69524

create procedure EmpDept2 
as 
Begin 
select Employees.EmployeeID
      , Employees.FirstName
      , Employees.LastName
      , Employees.Age
      , Employees.CreatedDate
      , Employees.LastModifiedDate
      , Employees.ModifiedBy
      , Employees.Active
      ,Department.DepartmentID
      , Department.DeptName 
from Employees inner join EmpDept 
on EmpDept.EmpId = Employees.EmployeeID 
inner join Department 
on EmpDept.DeptId= Department.DepartmentID
END

JOIN Two table at a time and then the condition ON what you are joining them, Then add One by one other tables again specifying the condition on which you want to join them.

Upvotes: 1

Related Questions