From: Michael Richards <michael@fastmail.ca>
To: pgsql-sql@postgresql.org
Subject: Left Joins...
Date: Sun, 27 May 2001 18:45:54 -0400 (EDT)
Message-ID: <3B1183A2.000107.09275@frodo.searchcanada.ca> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: michael@fastmail.ca
Subject: Re: Left Joins...
In-Reply-To: <3B1183A2.000107.09275@frodo.searchcanada.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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