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
Login
FAQ
Search