pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"left join" not working?
5+ messages / 3 participants
[nested] [flat]
* "left join" not working?
@ 2010-02-12 11:14 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-12 11:35 ` Re: "left join" not working? Oliveiros C, <oliveiros.cristina@marktest.pt>
2010-02-12 11:57 ` Re: "left join" not working? msi77 <msi77@yandex.ru>
0 siblings, 2 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-02-12 11:14 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "left join" not working?
2010-02-12 11:14 "left join" not working? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-12 11:35 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2010-02-12 11:57 ` Re: "left join" not working? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 5+ messages in thread
From: Oliveiros C, @ 2010-02-12 11:35 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
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" <vindex+lists-pgsql-sql@apartia.org>
To: <pgsql-sql@postgresql.org>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "left join" not working?
2010-02-12 11:14 "left join" not working? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-12 11:35 ` Re: "left join" not working? Oliveiros C, <oliveiros.cristina@marktest.pt>
@ 2010-02-12 11:57 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-12 12:17 ` Re: "left join" not working? Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-02-12 11:57 UTC (permalink / raw)
To: pgsql-sql
On Fri, Feb 12, 2010 at 11:35:02AM -0000, Oliveiros C, wrote:
> 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...
So, if I understand correctly, a WHERE filters all results regardless of
join conditions and can turn an OUTER JOIN into an INNER JOIN.
Thanks for pointing that out!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "left join" not working?
2010-02-12 11:14 "left join" not working? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-12 11:35 ` Re: "left join" not working? Oliveiros C, <oliveiros.cristina@marktest.pt>
2010-02-12 11:57 ` Re: "left join" not working? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-12 12:17 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 5+ messages in thread
From: Oliveiros C, @ 2010-02-12 12:17 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; pgsql-sql
Yes, because your WHERE is something that comes after the operation
of the LEFT JOIN, in practice, defeating the purpose you intented.
On your second query the p.id_line = 1 doesn't do that because it is part of
the LEFT JOIN itself..
HTH
Best,
Oliveiros d'Azevedo Cristina
----- Original Message -----
From: "Louis-David Mitterrand" <vindex+lists-pgsql-sql@apartia.org>
To: <pgsql-sql@postgresql.org>
Sent: Friday, February 12, 2010 11:57 AM
Subject: Re: [SQL] "left join" not working?
> On Fri, Feb 12, 2010 at 11:35:02AM -0000, Oliveiros C, wrote:
>> 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...
>
> So, if I understand correctly, a WHERE filters all results regardless of
> join conditions and can turn an OUTER JOIN into an INNER JOIN.
>
> Thanks for pointing that out!
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: "left join" not working?
2010-02-12 11:14 "left join" not working? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-12 11:57 ` msi77 <msi77@yandex.ru>
1 sibling, 0 replies; 5+ messages in thread
From: msi77 @ 2010-02-12 11:57 UTC (permalink / raw)
To: Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>; +Cc: pgsql-sql
> where p.id_line=1
this filters rows after join was applied. Try this
select c.id_currency,max(p.modified_on) from currency c left join
price_line p using (id_currency) where (p.id_line=1 or p.id_line is null) group by
c.id_currency;
> 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
>
Яндекс.Почта. Письма есть. Спама - нет. http://mail.yandex.ru/nospam/sign
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-02-12 12:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-12 11:14 "left join" not working? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-12 11:35 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2010-02-12 11:57 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-12 12:17 ` Oliveiros C, <oliveiros.cristina@marktest.pt>
2010-02-12 11:57 ` msi77 <msi77@yandex.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox