寻找有关如何构建具有多个映射的SQL查询的信息。

huangapple go评论60阅读模式
英文:

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

huangapple
  • 本文由 发表于 2023年2月13日 23:57:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75438285.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定