Re: Need Help for select

On Thu, 15 Aug 2002 11:17:15 +0900
"Masaru Sugawara" <rk73(at)sea(dot)plala(dot)or(dot)jp> wrote:

> On Wed, 14 Aug 2002 16:04:21 +0200
> Andre Schubert <andre(at)km3(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly