DetachedCriteria and simple joins

czuvich · **Joined:** Mon Mar 23, 2009 3:20 pm **Posts:** 4

Let's say I have an Employee class and a Request class. The request class has a RequestedBy field which is joined to an Employee class (which is represented by a database view). My Request class does not have any coded associations between RequestedBy and Employee. I am trying to create a reporting query via DetachedCriteria that joins the RequestedBy field to the Employee.NetworkID field.

How would I accomplish such a task? I have been fighting with this for quite some time. Thanks for any help.

wolli · **Posted:** Tue May 05, 2009 2:22 am

With criterias you can only use a subquery for filtering the result set. If you need a real join, you have to use HQL. Can you post the SQL you need ?

czuvich · **Joined:** Mon Mar 23, 2009 3:20 pm **Posts:** 4

Thanks for your reply.

SELECT R.RequestId, R.EventName, R.RequestedBy, E.FullName
FROM dbo.Request R
LEFT OUTER JOIN dbo.Employee E ON E.NetworkID = R.RequestedBy

I thought I could somehow add new associations in DetachedCriteria.

wolli · **Posted:** Tue May 05, 2009 12:26 pm

That kind of query is not possible with criterias (at least in NH 1.2, but I can't remember changes there in 2.0 or 2.1). You can only do subqueries qith criterias.

But you can always use a HQL query:

Code:

select r.RequestId, r.EventName, r.RequestedBy, e.Fullname
from Employee e, Request r where e.NetworkId = r.RequestedBy