Inner Join error (multi-part identifier could not be bound error only on using)

Question

I wrote the following query in my stored procedure on SQL SERVER 2008 : (I purposely truncated the Table names and also made the query easier to read)

1)

select Pr.Id from D D , DP DP, P P, Pr Pr
where D.Id = DP.Id AND DP.Id = P.Id AND P.Id = Pr.Id;

and it works completely fine , but when I write the same query using Inner Joins

2)

select Pr.PId  from D D
INNER JOIN DP DP ON D.Id = DP.Id
INNER JOIN P P ON DP.Id = P.Id 
INNER JOIN Pr Pr ON P.Id = Pr.Id

It throws an error : The multi-part identifier "Pr.Id" could not be bound.

3) I again tried another version :

select Pr.Id  from Pr Pr
INNER JOIN P P ON Pr.Id = P.Id
INNER JOIN DP DP ON P.Id =  DP.Id
INNER JOIN Dealer D ON DP.Id = D.Id

I fail to understand why 1 & 3 work whereas the 2 doesnt work . Although I feel all of them mean the same thing.

Answer 1

In 2) you have

select Pr.PId

While in 1) and 3) you have

select Pr.Id

does Pr has the PId column? or just a typo?

Answer 2

Do you need an alias on your Pr table? I can't tell as you have shorted your table names. Posting the code as it is will help us identify the problem.

Try:

select Pr.PId  from D D
INNER JOIN DP DP ON D.Id = DP.Id
INNER JOIN P P ON DP.Id = P.Id 
INNER JOIN Pr Pr ON P.Id = Pr.Id

Answer 3

Since the distinction between examples 2) and 3) is the location of the Pr table, and since your error references Pr.Id, my assumption is that you are trying to use a column from Pr before you reference the table in your sequence of joins.

A simple example:

select a.ID
from a
join b on a.id = b.id and a.id = c.id <--- referenced too early.
join c on c.id = b.cid

Would give you the error:

The multi-part identifier "c.id" could not be bound.

Since you're trying to reference the table alias before it appears in your sequence of joins.