R – NHibernate HQL Join Not Returning All Required Rows

hqlnhibernate

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

Here a few facts about the current schema:

  • An Estimate belongs to a Contract.
  • The OwningDepartment property of a Contract can be null.
  • The ParentBusinessStream property of a Department cannot be null

This is the query:

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 is not returning Estimates where the parent contract does not have an owning department. Instead I want the relevant fields to just be null. I tried a left outer join but got the same results.

Any help would be very much appreciated. Apologies if I've done something stupid.

Cheers,

James

Best Answer

i've found that unusual queries containing left outer joins are better off by using an ISQLQuery which gives you access to proper SQL syntax as well as some HQL power.

Besides that, you don't provide mapping files which usually are helpful

Related Topic