Skip site navigation (1) Skip section navigation (2)

Matching columns in rows from two tables

From: Richard Emberson <emberson(at)phc(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Matching columns in rows from two tables
Date: 2002-02-20 21:51:12
Message-ID: 3C741A50.A9FA6212@phc.net (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2002-02-20 23:21:41
Subject: Re: Dates and year 2000
Previous:From: Andy MardenDate: 2002-02-20 20:37:12
Subject: Re: Dates and year 2000

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group