在sql查询中的两个表之间建立几个关系是可能的吗?

问题描述

例如table1的两列，与table2的id列有关系

例如:

tbl_city:(id，标题)
tbl_person :( id，name，CurrentCityID，LastCityID，...)

现在，我想从tbl_person到tbl_city建立两个关系
CurrentCityID和LastCityID必须与tbl_city
相关
此查询是错误的:

选择tbl_person.name，tbl_city.title，tbl_city.title
来自tbl_person
加入tbl_city ON tbl_person.CurrentCityID = tbl_city.id
加入tbl_city到tbl_person.LastCityID = tbl_city.id

你能帮我吗?

for example two columns of table1 , have relation to the id column of table2

for example:

tbl_city: (id , title)
tbl_person: (id , name , CurrentCityID , LastCityID , ...)

now , I want to make two relations from tbl_person to tbl_city
CurrentCityID and LastCityID must relation to tbl_city

this query is wrong:

SELECT tbl_person.name , tbl_city.title , tbl_city.title
FROM tbl_person
JOIN tbl_city ON tbl_person.CurrentCityID = tbl_city.id
JOIN tbl_city ON tbl_person.LastCityID = tbl_city.id

can you help me ?

推荐答案

SELECT tbl_person.name , tc1.title , tc2.title
FROM tbl_person
JOIN tbl_city tc1 ON tbl_person.CurrentCityID = tc1.id
JOIN tbl_city tc2 ON tbl_person.LastCityID = tc2.id

SELECT tbl_person.name , C.title AS CurrentTitle, L.title AS LastCity
FROM tbl_person
JOIN tbl_city C ON tbl_person.CurrentCityID = C.id
JOIN tbl_city L ON tbl_person.LastCityID = L.id

这篇关于在sql查询中的两个表之间建立几个关系是可能的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！