From: Masaru Sugawara <rk73@sea.plala.or.jp>
To: Andre Schubert <andre@km3.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Need Help for select
Date: Thu, 15 Aug 2002 11:17:15 +0900
Message-ID: <20020815101638.C862.RK73@sea.plala.or.jp> (raw)
In-Reply-To: <20020814160421.2c75727e.andre@km3.de>
References: <20020814201310.C84C.RK73@sea.plala.or.jp>
<20020814160421.2c75727e.andre@km3.de>
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
Regards,
Masaru Sugawara
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@sea.plala.or.jp, andre@km3.de
Subject: Re: Need Help for select
In-Reply-To: <20020815101638.C862.RK73@sea.plala.or.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