英文:
Looking for information on how to construct an SQL query with multiple mappings
问题
我有一个主表，有两个附加的关联表，每个关系都有一个映射表，我已经对此进行了模糊处理并简化了情况。
| Person |
|---|
| id |
| name |
| Location |
|---|
| id |
| name |
| Person_Location |
|---|
| person_id |
| location_id |
| Nationality |
|---|
| id |
| name |
| Person_Nationality |
|---|
| person_id |
| nationality_id |
我想查询每个人并返回每个相关表的一行数据
人名, 地点名, 国籍名
一些相关表可能没有数据，我希望返回一行但在数据缺失的位置有一个空值。
我有一个使用INNER JOIN的查询，包括Person、Location和Person_Location映射表，但当我添加额外的Join用于Nationality映射表时，返回的行数比实际存在的行数多得多？我满意于一个联接，但对于额外的联接，我没有得到正确的结果。我似乎找不到如何做到这一点的示例，虽然可能没有使用正确的方法。
SELECT p.name, lo.name as location, na.name as nationality
FROM person p
INNER JOIN person_location pl ON p.id = pl.person_id
INNER JOIN location lo ON pl.location_id = lo.id
LEFT JOIN person_nationality pn ON p.id = pn.person_id
LEFT JOIN nationality na ON pn.nationality_id = na.id
英文:
I have a primary table with two additional linked tables, each relationship has a mapping table, I've obfuscated this and simplified the scenario.
| Person |
|---|
| id |
| name |
| Location |
|---|
| id |
| name |
| Person_Location |
|---|
| person_id |
| location_id |
| Nationality |
|---|
| id |
| name |
| Person_Nationality |
|---|
| person_id |
| nationality_id |
I'm looking to query each person and return one row for each related table
Person Name, Location Name, Nationilty Name
Some of the related tables may not be populated and I'd like the row returned but an empty value representing where the data is missing.
I've got a query with INNER JOINS for Person, Location and Person_Location mapping table but when I add additional joins for the Nationality mapping table, this returns many more rows than exists? I'm happy with a single set of joins but repeating this for additional one is not bearing fruit. I can't seem to find any examples of how to do this although I may not be using the correct approach.
SELECT p.name, lo.name as location, na.name as nationality
FROM person p
INNER JOIN person_location pl ON p.id = pl.person_id
INNER JOIN location lo ON pl.location_id = lo.id
INNER JOIN person_nationality pn ON p.id = pn.person_id
INNER JOIN nationality na ON pm.nationality_id = na.id
答案1
得分: 1
你可以使用两个标量子查询来获得你想要的结果。例如：
select p.name,
(select l.name from location l join person_location pl
on pl.location_id = l.id and pl.person_id = p.id limit 1) as location,
(select n.name from nationality n join person_nationality pn
on pn.nationality_id = n.id and pn.person_id = p.id limit 1) as nationality
from person p
英文:
You can use two scalar subqueries to get the result you want. For example:
select p.name,
(select l.name from location l join person_location pl
on pl.location_id = l.id and pl.person_id = p.id limit 1) as location,
(select n.name from nationality n join person_nationality pn
on pn.nationality_id = n.id and pn.person_id = p.id limit 1) as nationality
from person p
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论