PostgreSQL多对多关系查询：如何获取关联数据并追加宠物主名字字段

修改后的查询语句 #

你仅需要在原有查询的基础上，额外关联pet_owner表取出对应主人的first_name字段即可，完整语句如下：

SELECT
  pet.id AS id,
  pet.first_name AS pet_first_name,
  pet.last_name AS pet_last_name,
  pet_owner.first_name AS owner_first_name
FROM
  pet_petowner owner_pet
  JOIN pet ON owner_pet.pet_id = pet.id
  JOIN pet_owner ON owner_pet.owner_id = pet_owner.id
WHERE
  owner_pet.owner_id = 3;

注：上述语句对宠物和主人的名字字段加了区分度更高的别名，避免返回结果出现同名字段混淆，你可以根据实际需求调整别名规则。

额外补充：查询指定宠物对应的所有主人语句 #

你提到的第二类查询需求可以参考如下语句实现，将WHERE条件里的宠物ID替换为你要查询的目标ID即可：

SELECT
  pet_owner.id AS owner_id,
  pet_owner.first_name AS owner_first_name,
  pet_owner.last_name AS owner_last_name,
  pet.first_name AS pet_first_name
FROM
  pet_petowner owner_pet
  JOIN pet_owner ON owner_pet.owner_id = pet_owner.id
  JOIN pet ON owner_pet.pet_id = pet.id
WHERE
  owner_pet.pet_id = 目标宠物ID;

内容的提问来源于stack exchange，提问作者The silent one