From: Ian Barwick <barwick@gmx.net>
To: Joseph Shraibman <jks@selectacast.net>, pgsql-sql@postgresql.org
Subject: Re: How to join from two tables at once?
Date: Tue, 26 Aug 2003 05:46:19 +0200
User-Agent: KMail/1.5.1
References: <biebbh$2krq$1@news.hub.org>
In-Reply-To: <biebbh$2krq$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200308260546.20084.barwick@gmx.net>

On Tuesday 26 August 2003 02:58, Joseph Shraibman wrote:
> How can I join on one table with join conditions refering to two tables? 
> In this example p is missing an entry that corresponds to u.  I want to
> select from u and p, but have entries in u that don't have an entry in p. 
> The problem is I need to go through table a to get the corresponding value
> in table p, and a LEFT JOIN only operates on two tables. The subselect
> works, but in real life turns out to be a big performance drain.
(...)

> -- doesn't get 2, because there is no entry in p for it
> SELECT u.uid, u.txt, p.val FROM u,a,p WHERE u.uid = p.uid and a.id = u.aid
> and p.pkey = a.pkey;
>
> -- works, but uses a subselect
> SELECT u.uid, u.txt, (SELECT p.val FROM p WHERE p.uid = u.uid AND p.pkey =
> a.pkey) FROM u,a WHERE a.id = u.aid;
>
> --doesn't work: ERROR:  JOIN/ON clause refers to "u", which is not part of
> JOIN SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND
> p.pkey = a.pkey WHERE a.id = u.aid;

Try:

SELECT u.uid, u.txt, p.val
  FROM u
 INNER JOIN a ON (a.id=u.aid)
  LEFT JOIN p ON (p.pkey=a.pkey AND p.uid=u.uid)


Ian Barwick
barwick@gmx.net