Query with join in sql server 2008

Question

I have these tables:

1. Customer Details

   CustCode, Name , Address, CityID 
   

2. MasterCity

   CityName, CityId
   

3. OrderDetails

   OrderDetails, CustCode , OrderNo, Somedetails , DeliveryStation
   

Deliverystation is that where order has to be placed. Here I send CityId and Customer Details also contains CityId and not cityname.

Now I want to select data from OrderDetails. I join OrderDetails and Customer Details On Customer Details.CustCode = OrderDetails.CustCode and Customer Details and MasterCity on MasterCity.CityId = Customer Details.CityId.

this work fine but i also want to join OrderDetails and MasterCity to get DeliveryStation City Name.

How can I do this?

Answer 1

Just add another join to MasterCity with an alias, eg

Select a.CustCode, a.Name, a.Address, A.CityId,c.OrderNo, c.SomeDetails,d.CityName DeliveryStation
  from CustomerDetails a
  join MasterCity b
    on a.CityID=b.CityID
  join OrderDetail c
    on a.CustCode=c.CustCode
  join MasterCity d
    on d.CityId=c.DeliveryStation