CODEWITHSUNDEEP
sql-server
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

How to make inner join two times to single table in sql server

I have Two Table in sql server as

City_Master(
ID bigint,
Name  nvarchar(50)
)
Tour_Details(
ID bigint,
From_City bigint,
To_City bigint,
Details nvarchar(max),
Cost nvarchar(100),
Tour_Name nvarchar(100),
Duration nvarchar(max))

the columns in Tour_Details table From_City and To_City is coming from City_Master Table. I want to select the names of the cities of ID which is in From_City and To_City Column of Tour_Details Table.

I have used Following sql query for this

SELECT Tour_Details.ID, Tour_Details.Tour_Name, Tour_Details.From_City, Tour_Details.To_City, Tour_Details.Cost, 
   Tour_Details.Details,Tour_Details.Duration, City_Master.Name as City FROM Tour_Details INNER JOIN
   City_Master ON Tour_Details.From_City = City_Master.ID ORDER BY Tour_Details.ID DESC

But it is returning only one City Name but there are two City ID present in tour_Details Table columns as From_City,To_City. I want names both cities.Please can anyone help me

Upvotes: 0

Views: 49

Answers (1)

Ranadeera Kantirava
Ranadeera Kantirava

Reputation: 324

try this

SELECT Tour_Details.ID, Tour_Details.Tour_Name, Tour_Details.From_City, Tour_Details.To_City, Tour_Details.Cost, 
Tour_Details.Details,Tour_Details.Duration, B.Name as FromCityName, A.Name as ToCityName 
FROM Tour_Details 
INNER JOIN City_Master B ON B.ID  = Tour_Details.From_City
INNER JOIN City_Master A ON A.ID = Tour_Details.To_City 
ORDER BY Tour_Details.ID DESC

Upvotes: 2

Related Questions