title: nhibernate HQL select from two or multiple tables
body: <p>I have been trying to get results using HQL for two classes. It works for one table but does not work for two tables. </p>
  
  <p>The two entities below;</p>
  
  <pre><code>public class A_CUSTOMER
      {
          public virtual string CUSTOMER_NO { get; set; }
          public virtual string ADDRESS_LINE { get; set; }
  
          public virtual IList&lt;A_ACCOUNT&gt; ACCOUNTS { get; set; }
      }
  
  public class A_ACCOUNT
      {
          public virtual string AC_NO { get; set; }
          public virtual string CUST_NO { get; set; }
          public virtual string CCY { get; set; } 
          public virtual string AVL_BAL { get; set; }  
  
  
          public virtual A_CUSTOMER CUSTOMER { get; set; }
  
      }
  </code></pre>
  
  <p>Mappings for the classes below;</p>
  
  <pre><code>public class A_CUSTOMERMap:ClassMap&lt;A_CUSTOMER&gt;
      {
          public A_CUSTOMERMap()
          {
              Id(x =&gt; x.CUSTOMER_NO);
              Map(x =&gt; x.ADDRESS_LINE);
  
              HasMany(x =&gt; x.ACCOUNTS).KeyColumns.Add("CUST_NO");
          }
      }
  
  
  public class A_CUST_ACCOUNTMap:ClassMap&lt;A_CUST_ACCOUNT&gt;
      {
          public A_CUST_ACCOUNTMap()
          {
              Id(x =&gt; x.AC_NO);
                  Map(x=&gt;x.CUST_NO);
                  Map(x =&gt; x.AVL_BAL);
                  Map(x =&gt; x.CCY);
                  References(x =&gt; x.CUSTOMER,"CUST_NO");
  
          }
      }
  </code></pre>
  
  <p>Now, I have a code like this in my controller:</p>
  
  <pre><code>string myString = "1001,1002,1003,1004,1005";
  var indNumbers = myString.Split(',');
  string qry = @"select h.AC_NO,h.BRANCH_CODE,h.CUST_NO,h.CCY,h.AVL_BAL ,g.ADDRESS_LINE"+
                  "from A_ACCOUNT as h,A_CUSTOMER g "+ 
                  "where h.CUST_NO=g.CUSTOMER_NO AND h.AC_NO IN (:list)";
                  ;
  res = session.CreateQuery(qry)
        .SetParameterList("list", indNumbers)
        .List();
  </code></pre>
  
  <p>I have also tried different query string:</p>
  
  <pre><code>string qry = @"select h.AC_NO,h.BRANCH_CODE,h.CUST_NO,h.CCY,h.AVL_BAL ,g.ADDRESS_LINE"+
                 "from A_ACCOUNT as h left join A_CUSTOMER g on "+
                 "h.CUST_NO=g.CUSTOMER_NO"+ 
                 "where  h.AC_NO IN (:list)";
  </code></pre>
  
  <p>The error encountered below, any help will be much appreciated.</p>
  
  <pre><code>NHibernate.Hql.Ast.ANTLR.QuerySyntaxException was unhandled by user code
    HResult=-2146232832
    Message=Exception of type 'Antlr.Runtime.NoViableAltException' was thrown. near line 1, column 83
    Source=NHibernate
    StackTrace:
         at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
         at NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse()
         at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(String queryString, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory)
         at NHibernate.Engine.Query.HQLStringQueryPlan.CreateTranslators(String hql, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
         at NHibernate.Engine.Query.HQLStringQueryPlan..ctor(String hql, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
         at NHibernate.Engine.Query.HQLStringQueryPlan..ctor(String hql, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
         at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(String queryString, Boolean shallow, IDictionary`2 enabledFilters)
         at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(String query, Boolean shallow)
         at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString)
         at MvcApplication2.Controllers.CustAccountController.GetCustData(jQueryDataTableParamModel param) in ...
         at lambda_method(Closure , ControllerBase , Object[] )
         at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
         at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
         at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
         at System.Web.Mvc.Async.AsyncControllerActionInvoker.&lt;&gt;c__DisplayClass42.&lt;BeginInvokeSynchronousActionMethod&gt;b__41()
         at System.Web.Mvc.Async.AsyncResultWrapper.&lt;&gt;c__DisplayClass8`1.&lt;BeginSynchronous&gt;b__7(IAsyncResult _)
         at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
         at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
         at System.Web.Mvc.Async.AsyncControllerActionInvoker.&lt;&gt;c__DisplayClass37.&lt;&gt;c__DisplayClass39.&lt;BeginInvokeActionMethodWithFilters&gt;b__33()
         at System.Web.Mvc.Async.AsyncControllerActionInvoker.&lt;&gt;c__DisplayClass4f.&lt;InvokeActionMethodFilterAsynchronously&gt;b__49()
    InnerException: 
  </code></pre>