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

Re: simple LEFT JOIN giving wrong results ...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Marc G(dot) Fournier" <scrappy(at)hub(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: simple LEFT JOIN giving wrong results ...
Date: 2003-12-11 22:16:12
Message-ID: 15860.1071180972@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
"Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> So, I'm doing my LEFT JOIN wrong *somehow*, but its eluding me as to
> what/how .. :(

> ams=# select ct.ip_id, sum(ct.bytes) as traffic,
>              avg(cs.bytes)::bigint as storage
>         from company_00186.traffic ct
>    left join company_00186.storage cs ON ( ct.ip_id = cs.ip_id AND
>                                            month_trunc(cs.runtime) = '2003-12-01')
>        where month_trunc(ct.runtime) = '2003-12-01' group by ct.ip_id;

I suspect you do not want the month_trunc constraint to be included
in the JOIN ON condition, only in WHERE.

			regards, tom lane

In response to

Responses

pgsql-sql by date

Next:From: Marc G. FournierDate: 2003-12-11 23:54:42
Subject: Re: simple LEFT JOIN giving wrong results ...
Previous:From: Marc G. FournierDate: 2003-12-11 21:01:22
Subject: simple LEFT JOIN giving wrong results ...

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