根据第三个表中定义的关系联接两个表 [英] 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屋！