Skip site navigation (1) Skip section navigation (2)

Re: exclude part of result

From: Tarlika Elisabeth Schmitz <postgresql(at)numerixtechnology(dot)de>
To: Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
Subject: Re: exclude part of result
Date: 2008-06-27 11:22:38
Message-ID: 20080627122238.56cb6800@dick.coachhouse (view raw or flat)
Thread:
Lists: pgsql-sql
On Fri, 27 Jun 2008 11:33:07 +0200
Harald Fuchs <hari(dot)fuchs(at)gmail(dot)com> wrote:

> In article <20080627075136(dot)12add021(at)dick(dot)coachhouse>,
> Tarlika Elisabeth Schmitz <postgresql(at)numerixtechnology(dot)de> 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.






In response to

pgsql-sql by date

Next:From: Lennin CaroDate: 2008-06-27 14:17:11
Subject: Re: exclude part of result
Previous:From: Marc MaminDate: 2008-06-27 10:50:03
Subject: Re: exclude part of result

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group