Hi,

I need a special HQL Query and I do not find a way to formulate it. I'm using Hibernate with MySQL.

A group has many elements, and an element may belong to many groups.

Group <-(1,n)---(1,n)-> Element

Now, I'm searching those groups for a given set of elements which contain EXCLUSIVELY elements which are in the given set.

In order to illustrate the problem, here an example:

Table Element: E1, E2, E3, E4

Table Group: G1, G2, G3, G4

Join Table ElementGroup
G1 - E1
G1 - E2
G2 - E1
G2 - E2
G2 - E3
G3 - E3
G3 - E4
G4 - E1

So G1 has {E1, E2}, G2 has {E1, E2, E3}, G3 has {E3, E4} und G4 has
{E1}.

For the given set S={E1, E2} the query I'm searching for shall return {G1, G4}.

G2 may not be returned, because G2 does not contain E3.
G3 may not be returned, because G3 contains neither E3 nor E4.

A natural JOIN would return {G1, G2, G4}, so this is not the result I want.

I've found an SQL Query in native MySQL, but it is NOT possible to use native SQL in our application. But in order to understand the problem, I post the working query:

SELECT DISTINCT Group.id, Group.name
FROM Group
INNER JOIN ElementGroup EG1 ON EG1.Group_id = Group.id
AND EG1.Element_id IN (E1, E2) /* this is the given set */
LEFT JOIN ElementGroup EG2 ON EG2.Group_id = Group.id
AND EG2.Element_id NOT IN (E1, E2)
WHERE EG2.Element_id IS NULL;

I am unable to transform this MySQL Query to HQL.

I had another idea: I wanted to count twice and compare the count results in the having-clause, but although the HQL-Statement seems to be syntactically correct, MySQL throws a syntax Exception. (I think, MySQL does not like to count SQL-subqueries containing grouped variables in the having clause.)

Does anybody know how to solve the problem?

Thanks,

Christian Fischer