如何使用单个表两次内部联接 [英] How to Inner join with single tables twice

问题描述

大家好

我有三个表帐户，位置，部门

帐户
=========
ID
desr
from_Location_Id
To_Location_Id
From_Division_Id
To_Division_Id

位置
==========
locationId
位置

部门
=========
DivisionId
部门

我想要类似
的结果数据集
ID名称from_locationname to_locationname to_divisionname from_divisionname
我两次内部连接到同一张表，但是只有当from和to id相同时才出现条目，如何使用查询获取from和to代码的名称

在此先感谢
Amrutha

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

推荐答案

使用此

Use this

SELECT a.Id,a.desr,from_Location.Location,to_Location.Location,from_Division.Division,to_Division.Division
 FROM accounts a inner join location from_Location On a.from_Location_Id=from_Location.locationId
                 inner join location to_Location On a.To_Location_Id=to_Location.locationId
                 inner join Division from_Division On a.from_Division_Id=from_Division.DivisionId
                 inner join Division to_Division On a.To_Division_Id=to_Division.DivisionId

希望对您有所帮助，如果是，则接受并投票，否则将返回您的查询.

--Rahul D.

Hope this helps , If yes then accept it and vote it otherwise revert back with your queries.

--Rahul D.

大家好
我得到了解决办法，

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

这篇关于如何使用单个表两次内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！