NHibernate HQL Join Not Returning All Required Rows

I am modifying an existing HQL query which returns individual columns rather than an object 开发者_Python百科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

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

I think I have worked it out: d.ParentBusinessStream.Title is an implicit inner join but since d can be null it doesn't work correctly. I have changed my query to take this into account