sql - 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.

question from:https://stackoverflow.com/questions/2008853/join-two-tables-based-on-relationship-defined-in-third-table

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