Join two tables by comparing equal value on two columns

Question

For simplicity and clarity, assume I have these three tables.

Employee [ id ]

EmployeeName [ employeeId, name ]

EmployeeAddress [ employeeId, address ]

The only properties/relationships defined are in the 'sub-tables' EmployeeName & EmployeeAddress

EmployeeName BelongsTo Employee
EmployeeAddress BelongsTo Employee

There are no properties/relationships (collections) in the main Employee table such as

Employee HasMany EmployeeName
Employee HasMany EmployeeAdress.

I want to perform a join using DetachedCriteria between EmployeeName and EmployeeAddress only (not involving Employee), as such

select EmployeeName.name, EmployeeAddress.address
from EmployeeName inner join EmployeeAddress
where EmployeeName.employeeId = EmployeeAddress.employeeId and EmployeeAddress.address like '%somelocation%' order by EmployeeAddress.address

So your mappings don't have any relationships between Employee and EmployeeName/EmployeeAddress tables? — Vadim
Yads, yes there are no mappings from Employee to EmployeeName/EmployeeAddress (no HasMany relationships in Employee class). The inverse however is true (BelongsTo relationships in EmployeeName and EmployeeAddress classes). Basically I'm trying to avoid collections due to some business logic. — boyan
Mauricio, I can try HQL, but does this mean I have stumbled upon something that DetachedCriteria is unable to do vs HQL? It would be nice though if I can still use DetachedCriteria to have some standard way of doing things. So far all my queries have been in DetachedCriteria form. — boyan
HQL is easier for some things, and I think it would be useful here. Bottom line: use the right tool for the job. Don't try to shoehorn everything into Criteria (or NHibernate for that matter). — Mauricio Scheffer

Fredy Treboux Fredy Treboux · Accepted Answer · 2011-01-26T13:43:52

Not exactly the query you're looking for and haven't tested if it actually works.. but it may help you:

var addressCriteria = DetachedCriteria.For<EmployeeAddress>("ea")
                                      .Add(Restrictions.Like("ea.Address", "somelocation", MatchMode.Anywhere));
var nameCriteria = DetachedCriteria.For<EmployeeName>("en")
                                   .Add(Subqueries.PropertyIn("en.Employee", addressCriteria))
                                   .SetProjection(Projections.Property("en.Name"));

Join two tables by comparing equal value on two columns

1 Answers