Re: Matching columns in rows from two tables

On Wed, 20 Feb 2002 13:51:12 -0800
Richard Emberson <emberson(at)phc(dot)net> wrote:

> I have the following tables:
>

...

> -- a group set is a set of one or more groups
> CREATE TABLE group_sets (

Group_sets is already defined, isn't it ?

> group_id BIGINT,
> group_set_id BIGINT,
> ....
> FOREIGN KEY (group_id) REFERENCES groups (group_id),
> FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id)
> );
>
> I want to:
> Find the group_set_id such that for a given user_id (input parameter)
> there is a one-to-one correspondence between the group_ids associated
> with the user_id and the group_ids associated with the group_set_id;
>
> for every group_id that the user_id has, the group_set_id also has it
> and for every group_id that the group_set_id has, the user_id also has it.
> If there is no such group_set_id, then return null.
>
> What query will generate the group_set_id?

I'm not sure whether or not I have understood what you described.
If I use organizations instead of the 2nd group_sets you were created,
presumably ...

-- using no joins
select org1.group_set_id
from organizations as org1
where exists (select org0.group_id
from organizations as org0
where exists (select mem.group_id
from membership as mem
where org0.group_id = mem.group_id
and user_id = <<input parameter>>
group by mem.group_id
having count(*) = 1
)
and org1.group_id = org0.group_id
group by org0.group_id
having count(*) = 1
)
;

-- using inner joins
select org2.group_set_id
from (select org0.group_id
from (select group_id from membership
where user_id = <<input parameter>>
group by group_id
having count(*) = 1
) as mem
inner join organizations as org0
on (mem.group_id = org0.group_id)
group by org0.group_id
having count(*) = 1
) as org1
inner join organizations as org2
on (org1.group_id = org2.group_id)
;

Regards,
Masaru Sugawara