Grouping an joining

tgb · **Joined:** Mon May 19, 2008 7:45 am **Posts:** 4

Hi,

I have the following situation.
I have an entity Employee that consists of the attributes

-id (Primary key)
-loginname
-version
-email
-givenName
-lastName
-organizationalUnit

loginname and version are the natural identifier.

Everytime a user logs on to the application a LDAP query checks if the attributes are still correct. If one of the attibutes email, givenName,
lastName, organizationalUnit has changed the Employee will be versioned.

Now my problem:
I want to create a HQL query that gives me a collection of all Employees with their last version.
This is my SQL query that returns the IDs of the Employee objects:

Code:

SELECT 
    e2.id 
FROM 
   (SELECT loginname, max(version) AS version
    FROM Employee
    GROUP BY loginname) e1
    
    INNER JOIN 
    
   (SELECT loginname, version, id 
    FROM Employee) e2 ON e1.loginname= e2.loginname AND e1.version= e2.version

Now I want to use these IDs in an IN-clause of an HQL query that returns a list of objects of type Employee.

The base HQL query for the Employee list looks like:

Code:

FROM Employee e WHERE e.id IN (...)

Finally I have to merge the first query into the IN-condition of the second query. That's what's not working for me.

Any ideas?

Thank you in advance,
Thilo

mmerder · **Posted:** Wed Feb 04, 2009 8:39 am

How about

Code:

from Employee e1 where e1.version = (select max(e2.version) from Employee e2 where e1.loginName = e2.loginName)