postgresql Postgres 多重连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8779918/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres multiple joins
提问by user1137376
This is a postgres db. I am attempting to pull dog breed names (Cane Corso, Labrador, etc) from a breed table, to display based on the foreign keys located in an animal table. My issue is the animal table has two foreign keys to this single breed table, and I keep getting errors with my query. The first breed name will return based on a left join, but the second I cannot get the name to display as I already have a left join. Below is a simplified outline of what I am attempting to do:
这是一个 postgres 数据库。我试图从品种表中提取狗的品种名称（Cane Corso、拉布拉多等），以根据位于动物表中的外键进行显示。我的问题是动物表有两个指向这个单一品种表的外键，我的查询不断出错。第一个品种名称将根据左连接返回，但第二个我无法显示名称，因为我已经有了左连接。以下是我尝试做的事情的简化大纲：
breed table (ID, BreedName)
animal table (ID, breedID, breed2ID)
SELECT animal.ID, breed.BreedName FROM animal LEFT JOIN breed ON animal.breedID=breed.ID WHERE animal.ID='7';
What I need to do is also get the BreedName to join for animal.breed2ID which I am failing miserably at. I could easily hard code the breed names and have it display in the application, but this is not conducive to changes, additions, or deletions of breed names in the database.
我需要做的是让 BreedName 加入到我失败的animal.breed2ID 中。我可以轻松地对品种名称进行硬编码并将其显示在应用程序中，但这不利于数据库中品种名称的更改、添加或删除。
回答by kingdaemon
just do another join on that same table:
只需在同一张表上进行另一个连接：
SELECT animal.ID, breed1.BreedName as BreedName1, breed2.BreadName as BreadName2
FROM animal
LEFT JOIN breed as breed1 ON animal.breedID=breed1.ID
LEFT JOIN breed as breed2 ON animal.breedID=breed2.ID
WHERE animal.ID='7';
回答by Kuberchaun
While Ivan has solved your issue for your current database design a long term consideration or just a thing to learn from would be to make your table design more normalized so that having to add a new join every time you want to add a breed to an animal is not necessary. Through this simple design you have actually made your life more difficult.
虽然 Ivan 已经为您当前的数据库设计解决了您的问题，但一个长期的考虑或只是一个值得学习的事情是使您的表设计更加规范化，以便每次要向动物添加品种时都必须添加新的连接没有必要。通过这个简单的设计，您实际上使您的生活变得更加困难。
When you see repeated property types on an entity, in your case breedID and breed2ID you should typically start to smell something rotten in all but a very few rare cases.
当您在实体上看到重复的属性类型时，在您的品种品种 ID 和品种 2ID 的情况下，您通常应该开始闻到一些腐烂的气味，但极少数情况除外。
Under the ideal design you would do the following.
在理想的设计下，您将执行以下操作。
1.Keep your breed as is.
1.保持你的品种不变。
2.Make animal look like something like animal(animal_id,animal_name).
2.使动物看起来像animal(animal_id,animal_name)。
3.Add a new animal_breed table. It would look like this animal_breed(animal_breed_id,animal_id,breed_id). With animal_bread_id being a pk and a unique key on (animal_id,breed_id) with foreign keys pointing back to the respective tables.
3.添加一个新的animal_breed表。它看起来像这个animal_breed(animal_breed_id,animal_id,breed_id)。Animal_bread_id 是 pk 和 (animal_id,breed_id) 上的唯一键，外键指向相应的表。
This design allows a given animal to take on one or more breed types with out ever having to mess with your query to return the multiple breeds back. Your current design becomes a nightmare every time you have an animal with an extra breed. It has the potential to kill performance and make maintenance a nightmare and on top of it just isn't sound database design.
这种设计允许给定的动物采用一种或多种品种类型，而不必弄乱您的查询以返回多个品种。每当你拥有一个额外品种的动物时，你当前的设计就会变成一场噩梦。它有可能降低性能并使维护成为一场噩梦，而且最重要的是不是合理的数据库设计。
