Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 95F38632D64 for ; Fri, 12 Feb 2010 07:42:30 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 43064-05 for ; Fri, 12 Feb 2010 11:42:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 8A269635CE6 for ; Fri, 12 Feb 2010 07:35:05 -0400 (AST) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id Z83S9F4G; Fri, 12 Feb 2010 11:35:03 -0000 Message-ID: <34E07A4C4CDA4FFCB56CD63C675568ED@marktestcr.marktest.pt> From: "Oliveiros C," To: "Louis-David Mitterrand" , References: <20100212111453.GA16318@apartia.fr> Subject: Re: "left join" not working? Date: Fri, 12 Feb 2010 11:35:02 -0000 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 201002/44 X-Sequence-Number: 33643 My first guess is that NULL fails the condition on your WHERE clause, p.id_line = 1 So your WHERE clause introduces an additional level of filtering that filters out the NULLs coming from the LEFT JOIN... Didn't do any tests, it's just a guess... Best, Oliveiros ----- Original Message ----- From: "Louis-David Mitterrand" To: Sent: Friday, February 12, 2010 11:14 AM Subject: [SQL] "left join" not working? > Hi, > > This query: > > select c.id_currency,max(p.modified_on) from currency c left join > price_line p using (id_currency) where p.id_line=1 group by > c.id_currency; > > doesn't list all c.id_currency's, only those with a price_line. However > this one does: > > select c.id_currency,max(p.modified_on) from currency c left join > price_line p on (p.id_currency = c.id_currency and p.id_line=1) group by > c.id_currency; > > How come? > > Thanks, > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >