Re: Need Help for select

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

Regards,
Masaru Sugawara