Many to Many expression in HQL

Hermod Opstvedt · **Joined:** Wed Feb 18, 2004 2:57 am **Posts:** 3

I have a many to many relationship between two tables A and B. I am trying to figure out how to write a HQL statement for the following scenario :

Table a with attribute id and att1
Table b with attribute id and att2

The persisters for the two have a collection for the other ie a.b's and b.a's

Now how do i get the totalnumber of a's that have att1=x and where a.b's have att2=y

Doing it in good ol SQL is straight forward using the table that resolves the many-to-many rlationship

Hermod

emmanuel · **Posted:** Wed Feb 18, 2004 10:48 am

Code:

select distinct count(a) from A a join a.bs where a.att1= :x and b.att2 = :y

Not sure of the distinct count

Hermod Opstvedt · **Joined:** Wed Feb 18, 2004 2:57 am **Posts:** 3

emmanuel wrote:

Code:

select distinct count(a) from A a join a.bs where a.att1= :x and b.att2 = :y

Not sure of the distinct count

I tried this against the users/roles stuff in Tomcat authentication :

Query query = (Query) session.createQuery("select distinct count(users) from users in class ee.LogonUser join users.roles where users.userName='john' and roles.roleName = 'member'");

and got :

General error message from server: "Uknown table 'roles' in where clause".

Anybody else have a suggestion

This is against Adaptive Server Anywhere 6.04

michael · **Posted:** Wed Feb 18, 2004 5:12 pm

There is an alias missing and the syntax is deprecated. It should be:

Code:

select distinct count(users) from ee.LogonUser users join users.roles role where users.userName='john' and role.roleName = 'member'

Hermod Opstvedt · **Joined:** Wed Feb 18, 2004 2:57 am **Posts:** 3

That did the trick - thanks

Hermod