Date: Mon, 19 Aug 2002 07:31:42 +0200
From: Andre Schubert <andre@km3.de>
To: "Masaru Sugawara" <rk73@sea.plala.or.jp>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need Help for select
Message-Id: <20020819073142.4b714fcf.andre@km3.de>
In-Reply-To: <20020815101638.C862.RK73@sea.plala.or.jp>
References: <20020814201310.C84C.RK73@sea.plala.or.jp>
	<20020814160421.2c75727e.andre@km3.de>
	<20020815101638.C862.RK73@sea.plala.or.jp>
Organization: km3 teledienst GmbH
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Thu, 15 Aug 2002 11:17:15 +0900
"Masaru Sugawara" <rk73@sea.plala.or.jp> wrote:

> On Wed, 14 Aug 2002 16:04:21 +0200
> Andre Schubert <andre@km3.de> wrote:
> 
> 
> > I want to compare if a tuple in c exist in b for each c.d_id and b.a_id.
> > In c exists 3 tuples: (1,2), (3,4), (5)
> > and want to find these tuples in b.
> 
> 
> Probably I would think I have reached the correct query. Table b and c,
> however, must have unique indices like the following in order to get the
> result by using it, because it pays no attention to the duplicate keys.
> If there are no primary keys, it will become more complicated for eliminating
> duplicate keys. 
> 
> 
> create table b (
> a_id int,
> c_id int,
> constraint p_key_b primary key(a_id, c_id)
> );
> create table c (
> b_id int,
> d_id int,
> constraint p_key_c primary key(b_id, d_id)
> );
> 
> 
> SELECT a.name, d.name
> FROM (SELECT t2.a_id, t2.d_id
>         FROM (SELECT b.a_id, t1.d_id, t1.n
>                   FROM (SELECT c.b_id, c.d_id, t0.n
>                           FROM c, (SELECT d_id, COUNT(*) AS n
>                                      FROM c GROUP BY d_id) AS t0
>                          WHERE c.d_id = t0.d_id
>                        ) AS t1
>                        LEFT OUTER JOIN b ON (t1.b_id = b.c_id)
>                WHERE b.a_id IS NOT NULL
>               ) AS t2
>         GROUP BY t2.a_id, t2.d_id, t2.n
>        HAVING COUNT(*) = t2.n
>      ) AS t3,
>      a,
>      d
> WHERE a.id = t3.a_id
>   AND d.id = t3.d_id
> 

After days of studying this query and hours of testing i would say this query works for me very well.
Thank you very very much.

> 
> 
> Regards,
> Masaru Sugawara
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly