Join two tables based on relationship defined in third table

Question

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;