pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Masaru Sugawara <rk73@echna.ne.jp>
To: Richard Emberson <emberson@phc.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Matching columns in rows from two tables
Date: Sat, 23 Feb 2002 15:18:09 +0900
Message-ID: <20020223151139.DE3F.RK73@echna.ne.jp> (raw)
In-Reply-To: <3C741A50.A9FA6212@phc.net>
References: <3C741A50.A9FA6212@phc.net>

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




view thread (3+ messages)  latest in thread

Message-ID: <20020223151139.DE3F.RK73@echna.ne.jp>
Permalink:  ../20020223151139.DE3F.RK73@echna.ne.jp/
Also on:    postgresql.org/message-id/20020223151139.DE3F.RK73@echna.ne.jp

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: rk73@echna.ne.jp, emberson@phc.net
  Subject: Re: Matching columns in rows from two tables
  In-Reply-To: <20020223151139.DE3F.RK73@echna.ne.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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