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 30
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
WHER
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
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)
--- On Thu, 6/26/08, Tarlika Elisabeth
On Thu, Jun 26, 2008 at 05:06:14PM +0200, Marc Mamin wrote:
>
> Hello,
Hi Marc,
> I guess that the time offsets (now-21 and now-28) are evaluated each
> time the corresponding condition is met.
Excellent suggestion, this makes the query ~ 15% faster. Every bit
counts.
> It may be faster to put
On Fri, 27 Jun 2008 07:17:11 -0700 (PDT)
Lennin Caro <[EMAIL PROTECTED]> 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 navigatio
