With-clause referenced two different from-clause elements

I believe if you are getting this error that there is more likely something wrong with your configuration than with NHibernate. I received this error when I wanted to create a nested join between 2 unrelated tables. Something like this in HQL:

[sourcecode language="sql" padlinenumbers="true" autolinks="false" gutter="false" toolbar="false"]
select 
  tbl.CODE, 
  av1.NUM, 
  al1.CODE 
from 
  TABLE tbl 
   left outer join tbl.Class1 av1 with (av1.Classdef_Id=123456) 
   left outer join av1.Class2 al1 with (av1.NUM=al1.ID)
[/sourcecode]

or this SQL:

[sourcecode language="sql" autolinks="false" gutter="false" toolbar="false"]
select 
  tbl.id, 
  av1.num, 
  al1.code 
from 
  table tbl 
   left outer join Class1 av1 on (av1.Classdef_Id=123456) 
   left outer join Class2 al1 on (av1.num=al1.id)
[/sourcecode]

During my trouble shooting I received all of these 3 NHibernate error messages at different points times:

• Illegal attempt to dereference collection with element property reference
• With-clause referenced two different from-clause elements
• Path expected for join!

They really didn’t lead me in the path I needed to go. Searching for some reference or resolution lead me nowhere. I had almost given up and decided to implement a less optimal solution and I thought I would give it one more chance. I thought a lot about this error “illegal attempt to dereference collection with element property reference” and it made some since because I was trying to capture a specific value that was held in an IList within my class. I understood conceptually why I couldn’t do this. Error 1 and 2 made me move into the direction of the relationship mappings between tables. I.e. many-to-one, one-to-many, many-to-many and one-to-one. I starting thinking based on the fact that I was receiving the data in my class, I couldn’t access it because it was in an IList, that I needed to have a one-to-one relationship between my 2 tables. I should also note, that in this database domain there was no relationship (primary or foreign key) between these tables.

I found this very good example of how to map a one-to-one relationship in NHibernate here.

[sourcecode language="xml" autolinks="false" gutter="false" toolbar="false"]
<one-to-one name="Class1" class="Assembly.Class1" />
[/sourcecode]

I placed the one-to-one relationship attribute in the mapping file of the class referenced within the second (nested) join statement (Class2). By simply doing this I was able to get the HQL statement above to function as expected.