From: Renato De Giovanni <rdg@viafractal.com.br>
To: pgsql-sql@postgresql.org
Subject: Re: Left Joins...
Date: Wed, 30 May 2001 10:33:50 -0300
Message-ID: <3B14F6BD.B7CCD49A@viafractal.com.br> (raw)
References: <3B1183A2.000107.09275@frodo.searchcanada.ca>
> 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
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: rdg@viafractal.com.br
Subject: Re: Left Joins...
In-Reply-To: <3B14F6BD.B7CCD49A@viafractal.com.br>
* 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