pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMatching columns in rows from two tables
3+ messages / 2 participants
[nested] [flat]
* Matching columns in rows from two tables
@ 2002-02-20 21:51 Richard Emberson <emberson@phc.net>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Emberson @ 2002-02-20 21:51 UTC (permalink / raw)
To: pgsql-sql
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) REFERENCES users (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) 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?
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Matching columns in rows from two tables
@ 2002-02-23 06:18 Masaru Sugawara <rk73@echna.ne.jp>
parent: Richard Emberson <emberson@phc.net>
0 siblings, 1 reply; 3+ messages in thread
From: Masaru Sugawara @ 2002-02-23 06:18 UTC (permalink / raw)
To: Richard Emberson <emberson@phc.net>; +Cc: pgsql-sql
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 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Matching columns in rows from two tables
@ 2002-02-24 14:59 Masaru Sugawara <rk73@echna.ne.jp>
parent: Masaru Sugawara <rk73@echna.ne.jp>
0 siblings, 0 replies; 3+ messages in thread
From: Masaru Sugawara @ 2002-02-24 14:59 UTC (permalink / raw)
To: Richard Emberson <emberson@phc.net>; +Cc: pgsql-sql
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 there
are 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-02-24 14:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-20 21:51 Matching columns in rows from two tables Richard Emberson <emberson@phc.net>
2002-02-23 06:18 ` Masaru Sugawara <rk73@echna.ne.jp>
2002-02-24 14:59 ` Masaru Sugawara <rk73@echna.ne.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox