如何在具有不同值的相同行中两次检索联接关系 [英] How to retrieve joins relation two time in same rows with different value

问题描述

大家好......



你能帮我解决这个问题吗？

问题

i有两个相互关联的表两次

所以它们可能在同一行中出现两次，例如

相关值可能在第一行1和第二行2

但是当我加入时他们都有相同的价值（1）

这是错误的，

我很抱歉如果我不能解释得很好:(

i创建了这个例子，它显示了我的问题。

hello guys...

can you guys help me with this problem ?
the problems
i have two table that related to each other twice
so they may come two times in same rows for example
the related value may be in first rows 1 and in second rows 2
but when im joining them both comes with same value (1)
and that's wrong ,,
im sorry if i couldn't explain well :(
i created this example that's show exactly my problems.

 tblcity
+---------+------------+
| ID      | NAME       | 
+---------+------------+
|1        | paris      |
|2        | munchen    |
|3        | Berlin     |
+---------+------------+
tblmv
--this table is realted with tblcity twice once with from_city second to_city   
+---------+------------+-----------+--------+--------+
| ID      | NAME       |from_city  |to_city |status  |
+---------+------------+-----------+--------+--------+
| 1       | JAmes      | 1         |  2     |arrive  |
| 2       | Alex       | 2         |  3     |depart  |
+---------+------------+-----------+--------+--------+
view table be like 
+---------+------------+-----------+--------+-------+
| ID      | NAME       |from_city  |to_city |status |
+---------+------------+-----------+--------+-------+
| 1       | JAmes      | paris     |munchen |arrive |
| 2       | Alex       | munchen   |Berlin  |depart |
+---------+------------+-----------+--------+-------+

任何形式的帮助都将如此受到赞赏



我尝试过：



i尝试使用所有连接方式内连接左连接右连接

any kind of help will be so appreciated

What I have tried:

i tried with all joins way inner join left join right joins

推荐答案

要获得所需的结果，需要连接tblCity表两次。一次是From_City值，一次是To_City值。



你最终得到的是

To get the results you need, you need to join the tblCity table twice. Once for the From_City value and again for the To_City value.

What you end up with is something like

SELECT        dbo.tblmv.ID, dbo.tblmv.Name, dbo.tblmv.Status, tblCity_1.name AS from_City, dbo.tblCity.name AS To_City
FROM            dbo.tblCity INNER JOIN
                         dbo.tblmv ON dbo.tblCity.ID = dbo.tblmv.From_City INNER JOIN
                         dbo.tblCity AS tblCity_1 ON dbo.tblmv.To_City = tblCity_1.ID

给你结果





ID名称状态from_City To_City

1詹姆斯抵达巴黎慕尼黑
2 Alex离开柏林慕尼黑

which gives you results of


ID Name Status from_City To_City
1 James arrive Munchen Paris
2 Alex Depart Berlin Munchen

这篇关于如何在具有不同值的相同行中两次检索联接关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！