Clever Geek Handbook

NHibernate HQL Join without returning all required rows

I am modifying an existing HQL query that returns individual columns, not an object graph, but now I am not getting all the rows I need.

Here are some facts about the current scheme:

  • The assessment relates to the Contract.
  • The OwningDepartment property of the Contract can be null.
  • Department ParentBusinessStream property cannot be null

This is the request:

select e.ID, e.StatusCode.ID, e.InputDate, e.ParentClient.Name, e.ParentContractLocation.ParentLocation.Description, e.Description, e.InternalRef, e.ExternalRef, e.TotalIncTax, e.TaxTotal, e.Closed, e.ViewedByClient, e.HelpdeskRef, e.ParentContract.Reference, d.ParentBusinessStream.Title, d.Name
from Estimate e, Department d where (e.ParentContract.ID in (select cs.ParentContract.ID from ContractStaff cs
where cs.ParentStaff.ID=:staffID)) and ((d.ID = e.ParentContract.OwningDepartment.ID) OR (d.ID is null)) order by e.ID

Unfortunately my query does not return Grades where the parent contract does not have an ownership department. Instead, I want the corresponding fields to be zero. I tried left outer join but got the same results.

Any help would be much appreciated. I apologize if I did something stupid.

Greetings,

James

+2


source to share


2 answers


I think I worked it out: d.ParentBusinessStream.Title is an implicit inner join, but since d can be null, it doesn't work correctly. I modified my request to accommodate this.



0


source


I've found fancy queries containing left outer joins to be better using ISQLQuery, which gives you access to correct SQL syntax as well as some HQL power.



Also, you don't provide the mapping files which are usually useful

0


source






More articles:


All Articles