Обсуждение: Matching columns in rows from two tables

I have the following tables:

-- all of the users
-- user_id are unique
CREATE TABLE users (   user_id             BIGINT,   ....   PRIMARY KEY (user_id)
);
-- all of the groups
-- user_id are unique
CREATE TABLE groups (   group_id             BIGINT,   ...   PRIMARY KEY (group_id)
);
-- all of the group sets
-- group_set_id are unique
CREATE TABLE group_sets (   group_set_id        BIGINT,   ...   PRIMARY KEY (group_set_id)
);
-- a user can be a member of one or more groups
CREATE TABLE membership (   user_id             BIGINT,   group_id            BIGINT,   ....   FOREIGN KEY (user_id)
REFERENCESusers (user_id),   FOREIGN KEY (group_id) REFERENCES groups (group_id)
 
);

-- a group set is a set of one or more groups
CREATE TABLE group_sets (   group_id            BIGINT,   group_set_id        BIGINT,   ....   FOREIGN KEY (group_id)
REFERENCESgroups (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?

There ought to be some combination of joins, intersections, etc. that
can generate the result
but I am getting hungup on the fact that the number of group_ids being
matched is not fixed.

Thanks for any help.

Richard

On Wed, 20 Feb 2002 13:51:12 -0800
Richard Emberson <emberson@phc.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 org1where exists (select org0.group_id                from organizations
asorg0               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

On Sat, 23 Feb 2002 08:03:19 -0800
Richard Emberson <emberson@phc.net> wrote:

> Thank you very much for your help. I managed to create a query with only two select
> statements
> and none of the selects are inner-selects:
> 
> SELECT group_set_id, count(*) FROM group_sets , membership
> WHERE membership.user_id = <<input parameter>>
> AND membership.group_id = group_sets.group_id
> GROUP BY group_set_id
> INTERSECT
> SELECT group_set_id, count(*) FROM group_sets
> GROUP BY group_set_id;
> 
> There may still be a better (better performance) query,

Quite so.   I would think it's probably one of the best way.  Practically,I found my query including "where exists/not
exists"is awfully slow if thereare about 1000+ rows.
 


> but, again, this is the
> best I could
> come up with.
> 
> Thanks.
> 
> Richard
> 
> > On Wed, 20 Feb 2002 13:51:12 -0800
> > Richard Emberson <emberson@phc.net> wrote:
> >
> > > I have the following tables:
> > >
> >
> >  ...
> >
> > > -- a group set is a set of one or more groups
> > > CREATE TABLE group_sets (



Regards,
Masaru Sugawara