Create a View table Joining with Three tables

Debashish Mahonta

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?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gurpreet Arora2y ago
Accepted 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:
1. Employee e: This represents the Employee table (aliased as e).
2. Tour t: This represents the Tour table (aliased as t), which links Employee with Location through EID.
3. 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.
4. 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.
5. The CREATE VIEW statement defines the view MyViewsTable to store this result.
SELECT * FROM MyViewsTable;
+1

Vishal Joshi2y ago

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