On Thu, 15 Aug 2002 11:17:15 +0900 "Masaru Sugawara" <[EMAIL PROTECTED]> wrote:
> On Wed, 14 Aug 2002 16:04:21 +0200 > Andre Schubert <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
