根据第三个表中定义的关系联接两个表 [英] Join two tables based on relationship defined in third table
问题描述
我有两个表Activity和Action.可以为一项活动执行一个或多个动作.活动和动作之间的关系在第三个表活动动作"中给出.
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.
如何使用sql语句检索结果集,该结果集告诉我哪些操作适用于每个活动?这是表格结构
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
活动表 -ActivityId(PK),ActivityText
Activity Table -ActivityId(PK), ActivityText
操作表-ActionId(PK),ActionText
Action Table - ActionId(PK), ActionText
ActivityAction -ActivityActionId(PK),ActivityID,ActionID
ActivityAction -ActivityActionId(PK), ActivityID, ActionID
我想要一个结果表,格式为
I want a resultant table in the format
活动,适用措施
(活动"列应显示ActivityText,而适用动作"应显示ActionText)
(the Activity column should show ActivityText and Applicable Action should show ActionText)
你能指导我吗?
谢谢.
推荐答案
这应该可以解决问题
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
您应该阅读数据库中的JOINS.这是一个很好的起点:
You should read up on JOINS in databases. Here is a good starting point:
http://en.wikipedia.org/wiki/Join_%28SQL%29
基本上,这里是活动与动作之间的多对多关系,它使用称为ActivityAction的联接表通过两个一对多关系来解决.
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.
为了获取所需的数据,我们使用适当的PK和FK列将ActivityAction联接到每个表,然后在SELECT中选择字符串列
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
这篇关于根据第三个表中定义的关系联接两个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!