How do I get around a 'circular reference' in an Inner join

Question

Hi, I have the following 'circular dependency' in my Inner Join, any ideas how to get round it?

SELECT *FROM Reference 
INNER JOIN ReferenceInActivity ON Activity.ActivityID = ReferenceInActivity.ActivityID 
INNER JOIN @tbActivity AS Activity ON ReferenceInActivity.ReferenceID = Reference.ReferenceID

I get the error: Msg 4104, Level 16, State 1, Line 387 The multi-part identifier "Activity.ActivityID" could not be bound.

Answer 1

You are using Activity in the "on" statement before you've included it in the query in the "from" statement or a join statement. Switch your "on" statements around like this:

SELECT     * 
FROM       Reference 
INNER JOIN ReferenceInActivity 
ON         ReferenceInActivity.ReferenceID = Reference.ReferenceID
INNER JOIN @tbActivity AS Activity 
ON         Activity.ActivityID = ReferenceInActivity.ActivityID