Create a View table Joining with Three tables

Question

I have 3 tables below. I want to create a views table joining with three tables. Example:

Employee Table

EID	Name
001	Mr. Devid
002	Mr. Kamal

Location Table

LID	Location
1	Dhaka
2	Khulna
3	Rangpur

Tour Table

EID	DepartureID	ArrivalID
001	1	2
002	3	1

My Views Table

EID	Name	Departure	Arrival
001	Mr. Devid	Dhaka	Khulna
002	Mr. Kamal	Rangpur	Dhaka

How can i create the View table?

Gurpreet Arora · Answer

Query : CREATE VIEW MyViewsTable AS SELECT e.EID, e.Name, d.Location AS Departure, a.Location AS Arrival FROM Employee e JOIN Tour t ON e.EID = t.EID JOIN Location d ON t.DepartureID = d.LID JOIN Location a ON t.ArrivalID = a.LID; Explanation: Employee e : This represents the Employee table (aliased as e ). Tour t : This represents the Tour table (aliased as t ), which links Employee with Location through EID . Location d : This is the first join with the Location table (aliased as d ), which matches the DepartureID with LID to get the departure location. Location a : This is the second join with the Location table (aliased as a ), which matches the ArrivalID with LID to get the arrival location. The CREATE VIEW statement defines the view MyViewsTable to store this result. SELECT * FROM MyViewsTable;

Vishal Joshi · Answer

Hello Debashish, You need to use INNER JOIN To get the expected data. to create view you can use the below SQL. CREATE VIEW [dbo].[vw_Employee_Travel] AS SELECT t.EID, e.[Name], l_departure.[Location], l_arrival.[Location] FROM tblTour AS t INNER JOIN tblEmployee AS e ON t.EID = e.EID INNER JOIN tblLocation AS l_arrival ON t.ArrivalID = l_arrival.LID INNER JOIN tblLocation AS l_departure ON t.DepartureID = l_departure.LID GO Thanks Vishal Joshi

Create a View table Joining with Three tables

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)