Join two tables based on relationship defined in third table

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