Join two tables and then join with a third

Question

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:

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)

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

Answer 1

You almost have it, but need an alias and an ON clause for your second join insetad of the WHERE clause. Also, in your first ON clause, use the table alias a instead of the original name.

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 a.missionId = m.id
  LEFT OUTER JOIN
   (SELECT *
     FROM tTaxonomy
   ) t ON t.Tier1Mission = m.name AND t.Tier2Activity = a.name

However, looking over this, I see nothing requiring the use of joined subqueries. There are no aggregates or limits in the subqueries to necessitate them. You can just use plain table joins:

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 tMission m ON a.missionId = m.id
  LEFT JOIN tTaxonomy t ON t.Tier1Mission = m.name AND t.Tier2Activity = a.name

Answer 2

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 tMission m on a.missionId = m.MissionID
left outer join tTaxonomy t on t.Tier1Mission = m.name and t.Tier2Activity = a.name