Strange Error

[Squall_FF8 2]

Hey guys,

I'm using Microsoft SQL 2005. I have the following SQL:
 

Quote

select r.ID, r.Name, m.Name, rm.[View], rm.[Read], rm.[Write]

from Users_Role r, Users_Module m

  left join Users_RoleToModule rm on (rm.RoleID = r.ID and rm.ModuleID = m.ID)

where r.ID = 1

and it gives me the error:

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "r.ID" could not be bound.

Do you know why? It looks to me, like it doesnt recognize Users_Role r, in the left join (but it recognizes m.ID)

BTW the goal is - when I choose a Role (by ID) to get the rights (from Users_RoleToModule) set about all Modules (all record from it). Users_RoleToModule might not have records at all, or only some for the pairs <RoleID, ModuleID>.

Edited October 28 by Squall_FF8

[Pat Heuvel 2]

This looks like a many-to-many join with Users_RoleToModule as the link table, if so, try:

select r.ID, r.Name, m.Name, rm.[View], rm.[Read], rm.[Write]
from 
    Users_Role r left join Users_RoleToModule rm on rm.RoleId = r.ID
                 left join Users_Module m on rm.ModuleId = m.ID
where r.ID = 1

 

[Squall_FF8 2]

10 hours ago, Pat Heuvel said:

This looks like a many-to-many join with Users_RoleToModule as the link

Yes, many to many. Thank you for your code, That would come in handy later on. 

It seems to me, that when we have a product (Table x Table) in FROM clause, LEFT JOIN clause can't distinguish individual tables. So m in ON part is not  Users_Module, but rather the whole product (Role x Module).
Does anybody knows more about that? Why only in LEFT JOIN? is this MS SQL thing (bug?) or SQL standard?

[Pat Heuvel 2]

You're welcome. Your original code is incorrect basically because the comma separates the relation Users_Role (r) from the next relation, Users_Module left join Users_RoleToModule.

[Squall_FF8 2]

1 hour ago, Pat Heuvel said:

Your original code is incorrect basically because the comma separates the relation Users_Role (r) from the next relation, Users_Module left join Users_RoleToModule.

I dont understand that.
when you write: 

from TableA, TableB
  --that is equal to:
from TableA
  cross join TableB

but it seems, that is not the case for MS ..
BTW if I use cross join instead of comma it works..

[Pat Heuvel 2]

With help from grok:

**Short answer:**  
SQL-92 doesn't allow a mix of the old-style comma join (`TABLEA A, TABLEB B`) with a later `LEFT JOIN` in the same `FROM` clause. The comma is a join operator of its own (as you have observed), and the standard requires that **all joins after the first one use the explicit `JOIN` syntax** (with `ON` or `USING`).  

---
> Pick one style for the whole clause (all commas **or** all explicit `JOIN`s) or wrap the `JOIN` part in parentheses.

 

If you want the full explanation, I asked the following question:

"in a sql-92 from clause, why is this invalid: from tablea a, tableb b left join tablec c on c.id = a.id"

[Squall_FF8 2]

OK. Now it all make sense! Thank you very much for your patience, time  and the GREAT help!!!
I've seen in the help that all examples include explicit joins, but I didnt know that is required by the SQL-92.
Its time for me to move from old style to 92 😎