Join two tables based on relationship defined in third table

Question

Hello,

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.

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

Activity Table -ActivityId(PK), ActivityText

Action Table - ActionId(PK), ActionText

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)

could you please guide me?

Thank you.

Answer 1

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:

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.

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

Answer 2

SELECT ActivityText, ActionText
FROM Activity
JOIN ActivityAction ON Activity.ActivityId = ActivityAction.ActivityId
JOIN Action ON ActivityAction.ActionId = Action.ActionId
WHERE Activity.ActivityId = 1;

Answer 3

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