Обсуждение: exclude part of result
SELECT DISTINCT a, b, c, now(), count(item_pk) FROM product LEFT JOIN item ON item.product_fk = product_pk WHERE ... GROUP BY a, b, c I have another table 'navigation' which also has the columns a,b,c If the combination of (a,b,c) exists in 'navigation', then exclude it from above result. How can I achieve this? -- Best Regards, Tarlika Elisabeth Schmitz A: Because it breaks the logical sequence of discussion Q: Why is top posting bad?
am Fri, dem 27.06.2008, um 0:35:38 +0100 mailte Tarlika Elisabeth Schmitz folgendes: > > SELECT DISTINCT a, b, c, now(), count(item_pk) > FROM product > LEFT JOIN item ON item.product_fk = product_pk > WHERE ... > GROUP BY a, b, c > > > I have another table 'navigation' which also has the columns a,b,c > > If the combination of (a,b,c) exists in 'navigation', then exclude it > from above result. How can I achieve this? For instance with EXCEPT. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, 26 Jun 2008 19:49:01 -0400 (EDT) "Patricia Mitchell"<patricia-mitche@excite.com> wrote: > The columns (a,b,c) of the navigation table should not appear in the > result because you are only pulling records from the product and item > table. You are pulling the records out of the product and item table > with this statement: 'select a,b,c, count()....from product LEFT > JOIN item on....' > > > > P.M. > > --- On Thu 06/26, Tarlika Elisabeth Schmitz < > postgresql@numerixtechnology.de > wrote: > > From: Tarlika Elisabeth Schmitz [mailto: > postgresql@numerixtechnology.de] > > To: pgsql-sql@postgresql.org > > Date: Fri, 27 Jun 2008 00:35:38 +0100 > > Subject: [SQL] exclude part of result > > > SELECT DISTINCT a, b, c, now(), count(item_pk) > FROM product > LEFT JOIN item ON item.product_fk = product_pk > WHERE ... > GROUP BY a, b, c > > > I have another table 'navigation' which also has the columns a,b,c > > If the combination of (a,b,c) exists in 'navigation', then exclude it > from above result. How can I achieve this? Thank you for your response, Patricia. Maybe I did not express myself clearly enough: Example: 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]. -- Best Regards, Tarlika Elisabeth Schmitz A: Because it breaks the logical sequence of discussion Q: Why is top posting bad?
In article <20080627075136.12add021@dick.coachhouse>, Tarlika Elisabeth Schmitz <postgresql@numerixtechnology.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
Hi, Two other ideas... SELECT DISTINCT p.a, p.b, p.c, now(), count(item.item_pk) FROM product p JOIN (select distinct a,b,c from products except select distinct a,b,c from navigation )foo USING (a,b,c) LEFT JOIN item ON item.product_fk = product_pk WHERE ... GROUP BY p.a, p.b, p.c or maybe SELECT DISTINCT foo.*, now(), count(item.item_pk) FROM (select distinct a,b,c from products WHERE ... except select distinct a,b,c from navigation )foo LEFT JOIN item ON item.product_fk = product_pk WHERE ... GROUP BY p.a, p.b, p.c HTH, Marc Mamin
On Fri, 27 Jun 2008 11:33:07 +0200 Harald Fuchs <hari.fuchs@gmail.com> wrote: > In article <20080627075136.12add021@dick.coachhouse>, > Tarlika Elisabeth Schmitz <postgresql@numerixtechnology.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.
<table border="0" cellpadding="0" cellspacing="0"><tr><td style="font: inherit;" valign="top"><br />select * from (<br />SELECTDISTINCT a, b, c, now(), count(item_pk)<br />FROM product<br />LEFT JOIN item ON item.product_fk = product_pk<br/>WHERE ...<br />GROUP BY a, b, c<br />) t1 where not exists (select a,b,c, from navigation t2 where t2.a =t1.a and t2.b = t1.b and t2.c = t1.c)<br /><br />--- On <b>Thu, 6/26/08, Tarlika Elisabeth Schmitz <i><postgresql@numerixtechnology.de></i></b>wrote:<br /><blockquote style="border-left: 2px solid rgb(16, 16, 255);margin-left: 5px; padding-left: 5px;">From: Tarlika Elisabeth Schmitz <postgresql@numerixtechnology.de><br />Subject:[SQL] exclude part of result<br />To: pgsql-sql@postgresql.org<br />Date: Thursday, June 26, 2008, 11:35 PM<br/><br /><pre>SELECT DISTINCT a, b, c, now(), count(item_pk) <br />FROM product<br />LEFT JOIN item ON item.product_fk= product_pk<br />WHERE ...<br />GROUP BY a, b, c<br /><br /><br />I have another table'navigation' whichalso has the columns a,b,c<br /><br />If the combination of (a,b,c) exists in 'navigation', then exclude it<br />fromabove result. How can I achieve this?<br /><br />--<br /><br /><br />Best Regards,<br /><br />Tarlika Elisabeth Schmitz<br/><br /><br />A: Because it breaks the logical sequence of discussion<br />Q: Why is top posting bad? <br /><br/>-- <br />Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br />To make changes to your subscription:<br/>http://www.postgresql.org/mailpref/pgsql-sql</pre></blockquote></td></tr></table><br />
On Fri, 27 Jun 2008 07:17:11 -0700 (PDT) Lennin Caro <lennin.caro@yahoo.com> wrote: > > select * from ( > SELECT DISTINCT a, b, c, now(), count(item_pk) > FROM product > LEFT JOIN item ON item.product_fk = product_pk > WHERE ... > GROUP BY a, b, c > ) t1 where not exists (select a,b,c, from navigation t2 where t2.a = > t1.a and t2.b = t1.b and t2.c = t1.c) Many thanks - there seems to be half a dozen ways of achieving the desired result! > > --- On Thu, 6/26/08, Tarlika Elisabeth Schmitz > <postgresql@numerixtechnology.de> wrote: From: Tarlika Elisabeth > Schmitz <postgresql@numerixtechnology.de> Subject: [SQL] exclude part > of result To: pgsql-sql@postgresql.org > Date: Thursday, June 26, 2008, 11:35 PM > > SELECT DISTINCT a, b, c, now(), count(item_pk) > FROM product > LEFT JOIN item ON item.product_fk = product_pk > WHERE ... > GROUP BY a, b, c > > > I have another table 'navigation' which also has the columns a,b,c > > If the combination of (a,b,c) exists in 'navigation', then exclude it > from above result. How can I achieve this?