title: Join two tables and then join with a third
body: <p>I have a SQL query where I want to join two tables to gether and then use that joined table to join a third table.  I tried googling how to do this but it was awkward wording and found nothing useful.  Here is the code I am trying to use:</p>
  
  <pre><code>SELECT t.id AS ID
  , a.id AS ActivityID
  , t.ProjectType AS ProjectType
  , t.Tier1Mission AS Mission
  , m.id ASMissionID
  , m.name AS MissionName
  , t.Tier2Activity AS Activity
  , a.name AS ActivityName
  , t.Tier3Project AS Project
  FROM tActivity a
   INNER JOIN 
   (SELECT id, name FROM tMission) m
   ON tActivity.missionId = MissionID
   LEFT OUTER JOIN
   (SELECT *
    FROM tTaxonomy
    WHERE Tier1Mission = m.name AND Tier2Activity = a.name)
  </code></pre>
  
  <p>EDIT:
  The main problem I'm running into is that the tActivity table has entries that have the same 'name' but refer to different MissionId's.  Joining all of the tables is easy but it needs to have the right MissionID, the relationship is held in the third table 'tTaxonomy'.</p>