Hi, all:

I am using Hibernate 3.2.6 and MS SQL Server 2005. I need help on writing two Hibernate queries that seem complex to me. These are my tables:

1. ROLE - role_id (primary key), name
2. APPLICATION - app_id (primary key), name
3. PERMISSION - perm_id (primary key), role_id (foreign key), app_id (foreign key)
4. USER - user_id (primary key), name
5. USER_TO_PERMISSION - user_id (primary key, foreign key), perm_id (foreign key)

And these are my Java objects:
1. Role.java - it has a Collection of Permissions
2. Application.java - it has a Collection of Permissions
3. Permission.java - it has Role and Application properties.
4. User.java - it has a Collection of Permissions (in User.hbm I have defined it as bag)

I want to retrieve all roles (by app_id) assigned to a user, and this is what I have written:

SELECT DISTINCT p.role FROM USER u
LEFT JOIN FETCH u.permissions p
WHERE p.app_id = 1
AND u.user_id = 'jdoe';

Because I am still about 2 days away from compiling Java code, I do not know if the above query will return the expected results. Will it? The other query I need help on is this: I want to retrieve all roles (by app_id) *not assigned* to the user. How do I write it? Thanks.

All:

Never mind on this. I figured it out. Thanks again for at least viewing this thread.