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
Date: Mon, 19 Aug 2002 07:31:42 +0200
Message-ID: <20020819073142.4b714fcf.andre@km3.de> (raw)
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>
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
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: andre@km3.de, rk73@sea.plala.or.jp
Subject: Re: Need Help for select
In-Reply-To: <20020819073142.4b714fcf.andre@km3.de>
* 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