SQL 根据第三个表中定义的关系连接两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2008853/
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
Join two tables based on relationship defined in third table
提问by user176687
I have two tables Activity and Action. One or more actions can be performed for an activity. And the relationships between Activity and Action is given in a third table called Activity Action.
我有两个表 Activity 和 Action。可以为一项活动执行一个或多个操作。Activity 和 Action 之间的关系在名为 Activity Action 的第三个表中给出。
How do I retrieve a result set that tells me what action is applicable for each activity using an sql statement? Here's the table structure
如何使用 sql 语句检索一个结果集，该结果集告诉我哪些操作适用于每个活动？这是表结构
Activity Table-ActivityId(PK), ActivityText
活动表-ActivityId(PK), ActivityText
Action Table- ActionId(PK), ActionText
动作表- ActionId(PK), ActionText
ActivityAction-ActivityActionId(PK), ActivityID, ActionID
ActivityAction-ActivityActionId(PK), ActivityID, ActionID
I want a resultant table in the format
我想要一个格式的结果表
Activity, Applicable Action
活动，适用的行动
(the Activity column should show ActivityText and Applicable Action should show ActionText)
（Activity 列应显示 ActivityText，Applicable Action 应显示 ActionText）
could you please guide me?
你能指导我吗？
Thank you.
谢谢你。
回答by Raj More
This should do the trick
这应该可以解决问题
SELECT Activity.ActivityText as Activity, Action.ActionText as ApplicableAction
FROM ActivityAction
INNER JOIN Activity
ON ActivityAction.ActivityId = Activity.ActivityId
INNER JOIN Action
ON ActivityAction.ActionId = Action.ActionId
You should read up on JOINS in databases. Here is a good starting point:
您应该阅读数据库中的 JOINS。这是一个很好的起点：
http://en.wikipedia.org/wiki/Join_%28SQL%29
http://en.wikipedia.org/wiki/Join_%28SQL%29
Basically what we have here is a many to many relationship between Activity and Action which is resolved by two one-to-many relationships using the a join table called ActivityAction.
基本上，我们在这里拥有的是 Activity 和 Action 之间的多对多关系，这是通过使用称为 ActivityAction 的连接表的两个一对多关系来解决的。
To get the required data back, we are joining ActivityAction to each one of the tables using the appropriate PK and FK columns and then choosing the string columns in the SELECT
为了取回所需的数据，我们使用适当的 PK 和 FK 列将 ActivityAction 连接到每个表，然后在 SELECT 中选择字符串列
回答by mopoke
SELECT ActivityText, ActionText
FROM Activity
JOIN ActivityAction ON Activity.ActivityId = ActivityAction.ActivityId
JOIN Action ON ActivityAction.ActionId = Action.ActionId
WHERE Activity.ActivityId = 1;
回答by Gaim
SELECT ActivityText AS Activity, ActionText AS ApplicableAction
FROM Activity
JOIN ActivityAction on Activity.ActivityId = ActivityAction.ActivityID
JOIN Action on Action.ActionId = ActivityAction.ActionID
You have IDs in Action table and when you join table ActivityAction on ActivityId key then you are able to join activity table
您在 Action 表中有 ID，当您在 ActivityId 键上加入表 ActivityAction 时，您就可以加入活动表
