MIME-Version: 1.0
Message-Id: <3B1183A2.000107.09275@frodo.searchcanada.ca>
To: pgsql-sql@postgresql.org
Subject: Left Joins...
From: "Michael Richards" <michael@fastmail.ca>
Date: Sun, 27 May 2001 18:45:54 -0400 (EDT)
Content-Transfer-Encoding: 7bit
Content-Type: Text/Plain

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