pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLeft joins
10+ messages / 9 participants
[nested] [flat]
* Left joins
@ 1999-07-12 18:25 Peter Eisentraut <peter@pathwaynet.com>
0 siblings, 0 replies; 10+ messages in thread
From: Peter Eisentraut @ 1999-07-12 18:25 UTC (permalink / raw)
To: pgsql-sql
Does anyone know an elegant way of doing a left join like
Table 1
abc 1
def 2
ghi <NULL>
Table 2
1 foo
2 bar
3 duh
I'd like to join those to give me:
abc 1 foo
def 2 bar
ghi <NULL> <NULL>
I can currently only envision a way to do this via a union of several
queries filtering out null and non-null fields, but that gets very
cumbersome when you have lots of fields involved and more than two or
three tables to join.
Any suggestions?
Thanks.
--
Peter Eisentraut
PathWay Computing, Inc.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Left Joins...
@ 2001-05-27 22:45 Michael Richards <michael@fastmail.ca>
2001-05-30 13:33 ` Re: Left Joins... Renato De Giovanni <rdg@viafractal.com.br>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Richards @ 2001-05-27 22:45 UTC (permalink / raw)
To: pgsql-sql
I've got a select that pulls many values from the same table.
Basicaly for a given formid there can be many fields each one
depending on a definition. So form1 may be broken down as follows:
fieldid
1 firstname
2 lasname
3 postal code
Rather than sticking this data in XML (which is hard to query and
index) Each one occupies a row in my formdata table.
I've got a nasty query that joins a table onto itself like 22 times.
I'm wondering if there might be a better way to do this, and also how
I can left join every additional table on the first one. By this I
mean that if f1 matches my criteria and therefore isn't null, then
every other joined field will occur, null or not...
Here is a snippet of my query so you can see what I'm doing:
SELECT
f1.strval,f2.strval,f3.strval,f4.strval,f5.strval,f6.strval,f7.strval,
f8.strval,f9.strval,f10.strval,f11.strval,f12.strval,f13.strval,f14.st
rval
,f15.strval,f16.strval,f17.strval,f18.strval,f19.strval,f20.strval,m1.
strval
FROM formdata AS f1
LEFT JOIN formdata AS f2 ON (f2.formid=4 AND f2.occid=1 AND
f2.fieldid=2 AND f2.userid=f1.userid)
LEFT JOIN formdata AS f3 ON (f3.formid=4 AND f3.occid=1 AND
f3.fieldid=3 AND f3.userid=f1.userid)
LEFT JOIN formdata AS f4 ON (f4.formid=4 AND f4.occid=1 AND
f4.fieldid=4 AND f4.userid=f1.userid)
LEFT JOIN formdata AS f5 ON (f5.formid=4 AND f5.occid=1 AND
f5.fieldid=5 AND f5.userid=f1.userid)
LEFT JOIN formdata AS f6 ON (f6.formid=4 AND f6.occid=1 AND
f6.fieldid=6 AND f6.userid=f1.userid)
LEFT JOIN formdata AS f7 ON (f7.formid=4 AND f7.occid=1 AND
f7.fieldid=7 AND f7.userid=f1.userid)
LEFT JOIN formdata AS f8 ON (f8.formid=4 AND f8.occid=1 AND
f8.fieldid=8 AND f8.userid=f1.userid)
LEFT JOIN formdata AS f9 ON (f9.formid=4 AND f9.occid=1 AND
f9.fieldid=9 AND f9.userid=f1.userid)
[...]
So I don't care if f2..f22 do not exist, but f1 must exist...
Any ideas?
-Michael
_________________________________________________________________
http://fastmail.ca/ - Fast Free Web Email for Canadians
From pgsql-sql-owner@postgresql.org Sun May 27 21:44:51 2001
Received: from palle.girgensohn.se (c213.89.165.253.cm-upc.chello.se [213.89.165.253])
by postgresql.org (8.11.3/8.11.1) with ESMTP id f4S1bLA25902
for <pgsql-sql@postgresql.org>; Sun, 27 May 2001 21:37:21 -0400 (EDT)
(envelope-from girgen@partitur.se)
Received: from partitur.se (localhost [127.0.0.1])
by palle.girgensohn.se (8.11.3/8.11.3) with ESMTP id f4S1bHg62073;
Mon, 28 May 2001 03:37:17 +0200 (CEST)
(envelope-from girgen@partitur.se)
Message-ID: <3B11ABCD.DF857135@partitur.se>
Date: Mon, 28 May 2001 03:37:17 +0200
From: Palle Girgensohn <girgen@partitur.se>
Organization: Partitur
X-Mailer: Mozilla 4.76 [en] (X11; U; FreeBSD 4.3-STABLE i386)
X-Accept-Language: sv, en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Column reference X is ambiguous?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200105/285
X-Sequence-Number: 3113
Hi!
SELECT count(rf.userid) as count, rf.userid FROM read_faq rf,
faq f,
deltagare_saved d, person p WHERE rf.id = f.id AND f.kursid=20
AND
d.course_id = f.kursid AND d.userid = rf.userid AND
p.userid = d.userid GROUP BY userid
ERROR: Column reference "userid" is ambiguous
All of the tables have a userid, yes, but this query didn't
fail before 7.1.x. Is it really OK to fail in this case? I
thought SQL standard requires all GROUP|ORDER BY arguments to
acutally exist on the SELECT target list. Then, this makes me
puzzled... Are the joins making the userids get included in the
target in some hidden way?
/Palle
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Left Joins...
2001-05-27 22:45 Left Joins... Michael Richards <michael@fastmail.ca>
@ 2001-05-30 13:33 ` Renato De Giovanni <rdg@viafractal.com.br>
0 siblings, 0 replies; 10+ messages in thread
From: Renato De Giovanni @ 2001-05-30 13:33 UTC (permalink / raw)
To: pgsql-sql
> I've got a nasty query that joins a table onto itself like 22 times.
> I'm wondering if there might be a better way to do this, and also how
> I can left join every additional table on the first one. By this I
> mean that if f1 matches my criteria and therefore isn't null, then
> every other joined field will occur, null or not...
>
> Here is a snippet of my query so you can see what I'm doing:
> SELECT
> f1.strval,f2.strval,f3.strval,f4.strval,f5.strval,f6.strval,f7.strval,
> f8.strval,f9.strval,f10.strval,f11.strval,f12.strval,f13.strval,f14.st
> rval
> ,f15.strval,f16.strval,f17.strval,f18.strval,f19.strval,f20.strval,m1.
> strval
> FROM formdata AS f1
> LEFT JOIN formdata AS f2 ON (f2.formid=4 AND f2.occid=1 AND
> f2.fieldid=2 AND f2.userid=f1.userid)
> LEFT JOIN formdata AS f3 ON (f3.formid=4 AND f3.occid=1 AND
> f3.fieldid=3 AND f3.userid=f1.userid)
> LEFT JOIN formdata AS f4 ON (f4.formid=4 AND f4.occid=1 AND
> f4.fieldid=4 AND f4.userid=f1.userid)
> LEFT JOIN formdata AS f5 ON (f5.formid=4 AND f5.occid=1 AND
> f5.fieldid=5 AND f5.userid=f1.userid)
> LEFT JOIN formdata AS f6 ON (f6.formid=4 AND f6.occid=1 AND
> f6.fieldid=6 AND f6.userid=f1.userid)
> LEFT JOIN formdata AS f7 ON (f7.formid=4 AND f7.occid=1 AND
> f7.fieldid=7 AND f7.userid=f1.userid)
> LEFT JOIN formdata AS f8 ON (f8.formid=4 AND f8.occid=1 AND
> f8.fieldid=8 AND f8.userid=f1.userid)
> LEFT JOIN formdata AS f9 ON (f9.formid=4 AND f9.occid=1 AND
> f9.fieldid=9 AND f9.userid=f1.userid)
> [...]
>
> So I don't care if f2..f22 do not exist, but f1 must exist...
>
> Any ideas?
I'm not sure if I understood your problem, perhaps you want something like
this:
SELECT f1.strval AS val1,
(SELECT f2.strval FROM formdata f2
WHERE f2.formid=f1.formid
AND f2.occid=f1.occid
AND f2.fieldid=2
AND f2.userid=f1.userid) AS val2,
(SELECT f3.strval FROM formdata f3
WHERE f3.formid=f1.formid
AND f3.occid=f1.occid
AND f3.fieldid=3
AND f3.userid=f1.userid) AS val3,
(SELECT f4.strval FROM formdata f4
WHERE f4.formid=f1.formid
AND f4.occid=f1.occid
AND f4.fieldid=4
AND f4.userid=f1.userid) AS val4,...
FROM formdata f1
WHERE f1.formid=4 AND f1.occid=1 AND f1.fieldid=1
HTH,
--
Renato
Sao Paulo - SP - Brasil
rdg@viafractal.com.br
^ permalink raw reply [nested|flat] 10+ messages in thread
* left joins
@ 2005-07-06 10:01 Grant Morgan <grant@ryuuguu.com>
2005-07-06 10:30 ` Re: left joins Richard Huxton <dev@archonet.com>
2005-07-06 10:33 ` Re: left joins Nick Stone <nick@harelane.com>
2005-07-06 14:44 ` Re: left joins Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 10+ messages in thread
From: Grant Morgan @ 2005-07-06 10:01 UTC (permalink / raw)
To: pgsql-sql
I am having a problem with left joins in Postgresql.(probably my misunderstanding of left joins)
My first Query returns
70,000
select count(*)
from h
where h.tn > 20
and h.tn < 30
my left join
returns only 34,000
select count(*)
from h left join p using (r,pos)
where h.tn > 20
and h.tn < 30
and p.r_order=1
since it is a left join I though I should get a number no smaller in the left join than the original unjoined query. It seems to be acting like an inner join. Both of these are tables not views and both have hash indices on r column. I have tried left joins, right joins , and both using and on , nothing seems make a difference.
Questions
1)should a left join return atleast as many rows as the unjoined left table?
2)am I doing something wrong above?
3)If am not doing anything wrong is this postgresql problem and is there a work around?
Cheers,
Grant
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: left joins
2005-07-06 10:01 left joins Grant Morgan <grant@ryuuguu.com>
@ 2005-07-06 10:30 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 10+ messages in thread
From: Richard Huxton @ 2005-07-06 10:30 UTC (permalink / raw)
To: Grant Morgan <grant@ryuuguu.com>; +Cc: pgsql-sql
Grant Morgan wrote:
> I am having a problem with left joins in Postgresql.(probably my
> misunderstanding of left joins)
>
> My first Query returns
> 70,000
>
> select count(*)
> from h
> where h.tn > 20
> and h.tn < 30
>
> my left join
> returns only 34,000
>
> select count(*)
> from h left join p using (r,pos)
> where h.tn > 20
> and h.tn < 30
> and p.r_order=1
>
> since it is a left join I though I should get a number no smaller in the
> left join than the original unjoined query.
You don't. You make the left join, then apply another condition
"p.r_order=1". If you want to demonstrate a problem, you'll need to make
sure the queries are the same.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: left joins
2005-07-06 10:01 left joins Grant Morgan <grant@ryuuguu.com>
@ 2005-07-06 10:33 ` Nick Stone <nick@harelane.com>
2005-07-06 10:52 ` Re: left joins Grant Morgan <grant@ryuuguu.com>
2005-07-07 00:19 ` Re: left joins Ragnar Hafstað <gnari@simnet.is>
2 siblings, 2 replies; 10+ messages in thread
From: Nick Stone @ 2005-07-06 10:33 UTC (permalink / raw)
To: 'Grant Morgan' <grant@ryuuguu.com>; pgsql-sql
I've had exactly yhe same problem - try changing the query to.
select count(*)
from h left join p using (r,pos) and p.r_order=1
where h.tn > 20
and h.tn < 30
I think that should do it - the syntax you used would work in Oracle and MS
SQL but there's a subtle difference with the way Postgres works that means
that any NULLS in the right hand side of the join will be ignored
effectively making it an inner join
Hope this helps
Nick
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Grant Morgan
Sent: 06 July 2005 11:02
To: pgsql-sql@postgresql.org
Subject: [SQL] left joins
I am having a problem with left joins in Postgresql.(probably my
misunderstanding of left joins)
My first Query returns
70,000
select count(*)
from h
where h.tn > 20
and h.tn < 30
my left join
returns only 34,000
select count(*)
from h left join p using (r,pos)
where h.tn > 20
and h.tn < 30
and p.r_order=1
since it is a left join I though I should get a number no smaller in the
left join than the original unjoined query. It seems to be acting like an
inner join. Both of these are tables not views and both have hash indices on
r column. I have tried left joins, right joins , and both using and on ,
nothing seems make a difference.
Questions
1)should a left join return atleast as many rows as the unjoined left table?
2)am I doing something wrong above?
3)If am not doing anything wrong is this postgresql problem and is there a
work around?
Cheers,
Grant
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: left joins
2005-07-06 10:01 left joins Grant Morgan <grant@ryuuguu.com>
2005-07-06 10:33 ` Re: left joins Nick Stone <nick@harelane.com>
@ 2005-07-06 10:52 ` Grant Morgan <grant@ryuuguu.com>
1 sibling, 0 replies; 10+ messages in thread
From: Grant Morgan @ 2005-07-06 10:52 UTC (permalink / raw)
To: pgsql-sql
Thank you Richard and Nick, your right.
And what Nick showed below is what I wanted.
Cheers,
Grant
On Wed, 06 Jul 2005 19:33:03 +0900, Nick Stone <nick@harelane.com> wrote:
> I've had exactly yhe same problem - try changing the query to.
>
> select count(*)
> from h left join p using (r,pos) and p.r_order=1
> where h.tn > 20
> and h.tn < 30
>
> I think that should do it - the syntax you used would work in Oracle and MS
> SQL but there's a subtle difference with the way Postgres works that means
> that any NULLS in the right hand side of the join will be ignored
> effectively making it an inner join
>
> Hope this helps
>
> Nick
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of Grant Morgan
> Sent: 06 July 2005 11:02
> To: pgsql-sql@postgresql.org
> Subject: [SQL] left joins
>
> I am having a problem with left joins in Postgresql.(probably my
> misunderstanding of left joins)
>
> My first Query returns
> 70,000
>
> select count(*)
> from h
> where h.tn > 20
> and h.tn < 30
>
> my left join
> returns only 34,000
>
> select count(*)
> from h left join p using (r,pos)
> where h.tn > 20
> and h.tn < 30
> and p.r_order=1
>
> since it is a left join I though I should get a number no smaller in the
> left join than the original unjoined query. It seems to be acting like an
> inner join. Both of these are tables not views and both have hash indices on
> r column. I have tried left joins, right joins , and both using and on ,
> nothing seems make a difference.
> Questions
> 1)should a left join return atleast as many rows as the unjoined left table?
> 2)am I doing something wrong above?
> 3)If am not doing anything wrong is this postgresql problem and is there a
> work around?
>
>
> Cheers,
> Grant
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: left joins
2005-07-06 10:01 left joins Grant Morgan <grant@ryuuguu.com>
2005-07-06 10:33 ` Re: left joins Nick Stone <nick@harelane.com>
@ 2005-07-07 00:19 ` Ragnar Hafstað <gnari@simnet.is>
2005-07-07 01:11 ` Re: left joins Tony Wasson <ajwasson@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Ragnar Hafstað @ 2005-07-07 00:19 UTC (permalink / raw)
To: pgsql-sql
On Wed, 2005-07-06 at 11:33 +0100, Nick Stone wrote:
> I've had exactly yhe same problem - try changing the query to.
>
> select count(*)
> from h left join p using (r,pos) and p.r_order=1
> where h.tn > 20
> and h.tn < 30
really ? is this legal SQL ?
is this a 8.0 feature ?
I get syntax error at or near "and" at character 41
gnari
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: left joins
2005-07-06 10:01 left joins Grant Morgan <grant@ryuuguu.com>
2005-07-06 10:33 ` Re: left joins Nick Stone <nick@harelane.com>
2005-07-07 00:19 ` Re: left joins Ragnar Hafstað <gnari@simnet.is>
@ 2005-07-07 01:11 ` Tony Wasson <ajwasson@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tony Wasson @ 2005-07-07 01:11 UTC (permalink / raw)
To: Ragnar Hafstað <gnari@simnet.is>; +Cc: pgsql-sql
On 7/6/05, Ragnar Hafstað <gnari@simnet.is> wrote:
> On Wed, 2005-07-06 at 11:33 +0100, Nick Stone wrote:
> > I've had exactly yhe same problem - try changing the query to.
> >
> > select count(*)
> > from h left join p using (r,pos) and p.r_order=1
> > where h.tn > 20
> > and h.tn < 30
>
> really ? is this legal SQL ?
> is this a 8.0 feature ?
> I get syntax error at or near "and" at character 41
>
SQL like this works for me when I write it with an ON statement
instead of a USING.
select count(*)
from h left join p on (h.r=p.r and h.pos=r.pos and p.r_order=1)
where h.tn > 20
and h.tn < 30
Filtering within the join condition is very useful when doing a left outer join.
Here's another example "from the book" doing this type of filter
within the join:
http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html
Tony Wasson
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: left joins
2005-07-06 10:01 left joins Grant Morgan <grant@ryuuguu.com>
@ 2005-07-06 14:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2005-07-06 14:44 UTC (permalink / raw)
To: Grant Morgan <grant@ryuuguu.com>; +Cc: pgsql-sql
"Grant Morgan" <grant@ryuuguu.com> writes:
> select count(*)
> from h left join p using (r,pos)
> where h.tn > 20
> and h.tn < 30
> and p.r_order=1
> since it is a left join I though I should get a number no smaller in
> the left join than the original unjoined query. It seems to be acting
> like an inner join.
Well, yeah. The condition p.r_order=1 will return NULL (effectively
FALSE) for any row in which p.r_order is NULL, so none of the
null-extended rows can survive the WHERE filter, so it's effectively
an inner join. Recent versions of PG actively recognize this case
and reduce the LEFT JOIN to plain JOIN, but even if we did not do that
you'd get the same result.
I've heard it claimed that Oracle produces different results; if true,
it must have something to do with their rather standards-challenged
interpretation of NULL ...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-07-07 01:11 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-12 18:25 Left joins Peter Eisentraut <peter@pathwaynet.com>
2001-05-27 22:45 Left Joins... Michael Richards <michael@fastmail.ca>
2001-05-30 13:33 ` Re: Left Joins... Renato De Giovanni <rdg@viafractal.com.br>
2005-07-06 10:01 left joins Grant Morgan <grant@ryuuguu.com>
2005-07-06 10:30 ` Re: left joins Richard Huxton <dev@archonet.com>
2005-07-06 10:33 ` Re: left joins Nick Stone <nick@harelane.com>
2005-07-06 10:52 ` Re: left joins Grant Morgan <grant@ryuuguu.com>
2005-07-07 00:19 ` Re: left joins Ragnar Hafstað <gnari@simnet.is>
2005-07-07 01:11 ` Re: left joins Tony Wasson <ajwasson@gmail.com>
2005-07-06 14:44 ` Re: left joins Tom Lane <tgl@sss.pgh.pa.us>
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