On Fri, 27 Jun 2008 11:33:07 +0200 Harald Fuchs <[EMAIL PROTECTED]> wrote:
> In article <[EMAIL PROTECTED]>, > Tarlika Elisabeth Schmitz <[EMAIL PROTECTED]> writes: > > > PRODUCT table : > > > A B C > > 100 200 300 > > 100 200 301 > > 100 205 300 > > 100 205 301 > > > NAVIGATION table > > A B C #ITEMS > > 100 200 300 5 > > 100 200 301 6 > > > My query needs to return > > 100 205 300 #items > > 100 205 301 #items > > so I can insert them in NAVIGATION. NAVIGATION must not contain any > > duplicate combinations of [a,b,c]. > > Just use another LEFT JOIN to filter out the corresponding product > lines: > > SELECT DISTINCT a, b, c, now(), count(item_pk) > FROM product > LEFT JOIN navigation USING (a, b, c) > LEFT JOIN item ON item.product_fk = product_pk > WHERE navigation.a IS NULL > GROUP BY a, b, c Harald, Marc - thank you for your responses! That does the trick. The USING construct was new to me. I notice from the manual that it is is equivalent to ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c). My objective is to insert the missing a,b,c combinations into NAVIGATION: INSERT INTO navigation (a, b, c, save_time, item_no) SELECT ... I need to replicate the above for a,b,c + a,b + a: NAVIGATION will really contain 100 - - 11 100 200 - 11 100 200 300 5 100 200 301 6 Some other questions spring to mind: Which indices should I define? PRODUCT has a few thousand rows, ITEM will grow over time, NAVIGATION will have a few hundred entries. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
