the multi-part identifier could not be bound - inner join of 5 tables - no subqueries

Question

I've been stuck on this error and the related questions found here on SO have not helped me see the light on this query:

SELECT 
 EventID = a.EventID
,PositionID = a.PositionID
,SubscriberID = a.SubscriberID
,EventTitle = a.EventTitle
,Frequency = a.Frequency
,DueDate = a.DueDate
,NoticeDate = a.NoticeDate
,SubscriberName = e.SubscriberName 
,PositionTitle = d.PositionTitle
,UserID = c.UserID
,UserName = c.UserName
,BackupUserID = b.BackupUserID
FROM 
    dbo.tblEvent         a JOIN 
    dbo.PositionPeople   b JOIN
    dbo.UserInfo         c JOIN 
    dbo.Position         d JOIN
    dbo.Subscriber       e
ON
a.PositionID        = b.PositionID      AND
a.SubscriberID      = b.SubscriberID    AND
b.IncumbentUserID   = c.UserID          AND
a.SubscriberID      = c.SubscriberID    AND
a.SubscriberID      = e.SubscriberID    AND
a.PositionID        = d.PositionID  

I'm not mixing implicit and explicit JOINs and not using a subquery as suggested by other threads on this topic. I get this error for everything following the ON clause above (except for e.SubscriberID and d.PositionID). I've tried several variations and I cannot spot the problem.

Answer 1

Your query structure seems to be inconsistent, try this version of the same query:

SELECT A.EventID
    ,A.PositionID
    ,A.SubscriberID
    ,A.EventTitle
    ,A.Frequency
    ,A.DueDate
    ,A.NoticeDate
    ,E.SubscriberName 
    ,D.PositionTitle
    ,C.UserID
    ,C.UserName
    ,B.BackupUserID
FROM dbo.tblEvent A
INNER JOIN dbo.PositionPeople B ON B.PositionID = A.PositionID
                                  AND B.SubscriberID = A.SubscriberID
INNER JOIN dbo.UserInfo C ON C.SubscriberID = A.SubscriberID
                            AND C.UserID = B.IncumbentUserID
INNER JOIN dbo.Position D ON D.PositionID = A.PositionID
INNER JOIN dbo.Subscriber E ON E.SubscriberID = A.SubscriberID

And if you want to set some variables using this query, simply add the variable instantiation in the SELECT cause as in the following example:

SELECT @EventID = A.EventID
    ,@PositionID = A.PositionID
    ...
FROM ...

Hope this will help.