Find not referenced Objects

Jaraz · **Joined:** Sat Oct 11, 2003 6:34 am **Posts:** 9

Hello,

ich have a n:m Relation between Person and Role, with an intermediate Class Member. (2 one-to-many)

How can I fetch all Roles where a Person is NOT a Member?

Regards
Christian
----
Database Postgresql
Hibernate 2.1.6

Tables:

create table PERSON (
PERSON_ID int8 not null,
name varchar(255),
primary key (PERSON_ID)
);
create table MEMBER (
MEMBER_ID int8 not null,
PERSON_ID int8 not null,
ROLE_ID int8 not null,
primary key (MEMBER_ID)
);
create table ROLE (
ROLE_ID int8 not null,
name varchar(255),
primary key (ROLE_ID)
);

shl · **Posted:** Mon Aug 23, 2004 6:21 am

in SQL it should look like:

Code:

select role.* from Person person
   right join Member member on member.person_id=person.person_id
   right join Role role on role.role_id=member.role_id
where person.person_id<>100 or member.role_id is null

in HQL:

Code:

select role from Person person
   right join person.membershipCollection  member
   right join member.role role
where person.id<>100 or member is null

If you have in Role a collection of Persons, use similar queries but with left join

Regards