How to Inner join with single tables twice

Posted by Amritha444 under Sql Server on 2/4/2012 | Points: 10 | Views : 1865 | Status : [Member] | Replies : 1
hi all

I have three tables accounts,location,division

accounts
=========
Id
desr
from_Location_Id
To_Location_Id
From_Division_Id
To_Division_Id

location
==========
locationId
location

Division
=========
DivisionId
Division

I want result data set like

Id Name from_locationname to_locationname to_divisionname from_divisionname
I inner joined to same table twice but entries coming only if from and to ids are same how to get names of from and to codes using query

Thanks in Advance
Amrutha




Responses

Posted by: Amritha444 on: 2/4/2012 [Member] Starter | Points: 25

Up
0
Down
hi all i got solution as

SELECT accounts.Id, accounts.Name, location_1.location as from_locationName, location.location AS To_location,
Division.Division As from_Division, Division_1.Division AS To_Division
FROM location AS location_1 RIGHT OUTER JOIN
Division RIGHT OUTER JOIN
accounts LEFT OUTER JOIN
Division AS Division_1 ON accounts.To_Division_Id = Division_1.DivisionId ON Division.DivisionId = accounts.From_Division_Id LEFT OUTER JOIN
location ON accounts.To_Location_Id = location.locationId ON location_1.locationId = accounts.from_Location_Id


Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response