Access Nested inner join in a right join

Question

I am trying to nest an inner join in access inside a right join The first right join query is

SELECT * FROM ProjectMilestone AS a
RIGHT JOIN ProjectMilestone AS b ON a.PredecessorMilestone = 
b.ProjectMilestoneID

Where when joined the first select statement would have d.MilestoneName

The second inner join query is

SELECT d.MilestoneName FROM ProjectMilestone AS c INNER JOIN Milestone AS d 
ON c.MilestoneID = d.MilestoneID

What I read with access is that there is an issue with just doing an inner join after the right join so I'm not sure what to do. Any help would be greatly appreciated. Thanks.

Sample Data:

ProjectMilestone Table
ProjectMilestoneID, MilestoneID, etc etc, PredecessorMilestone (which is linked to ProjectMilestoneID on a 0-1 to 1 cardinality)
79, 1 , , Null
80, 2, , 79
81, 3, , 80

Milestone Table
MilestoneID, MilestoneName
1, Handover
2, Deposit
3, Handover Panels

So the results I am after are:

Null 
Handover 
Deposit

So for second result (Handover): which in the ProjectMilestone Table is entry ProjectMilestoneID 80, the PredecessorMilestone is 79, then get the ProjectMilestoneID 79 via a right join to another instance of the ProjectMilestone Table (because it is linked to the same table) to find the MilestoneID value (1) from row where ProjectMilestoneID value 79, then find the corresponding MilestoneName entry in the Milestone Table for MilestoneID 1 Which is Handover.

Hopefully this makes more sense.

Answer 1

In general, you don't mix inner and outer joins like that. You start with the table whose rows you want to keep. Then add LEFT JOINs for all the other tables.

If you want the milestone name for the first milestone, you can do:

SELECT pm.*, pmp.*, m.MilestoneName
FROM (ProjectMilestone AS pm LEFT JOIN
      ProjectMilestone AS pmp
      ON pm.PredecessorMilestone = pmp.ProjectMilestoneID
     ) LEFT JOIN
     Milestone AS m
     ON pm.MilestoneID = m.MilestoneID;

You can add another LEFT JOIN if you want the names of both milestones.