Omni
Omni

Reputation: 337

Join two tables and then join with a third

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

Upvotes: 6

Views: 16840

Answers (3)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171511

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

Upvotes: 2

Taryn
Taryn

Reputation: 247830

It looks like you were close, you had the JOIN syntax in the wrong place on the last JOIN:

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

Or even:

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 = MissionID
LEFT OUTER JOIN tTaxonomy t
    on m.name = t.Tier1Mission
    AND a.name = t.Tier2Activity

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270687

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

Upvotes: 5

Related Questions