Reputation: 337
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
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
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
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