Received: from huey.jpl.nasa.gov (huey.jpl.nasa.gov [128.149.68.100])
	by hub.org (8.10.1/8.10.1) with ESMTP id e5Q49l176702
	for <pgsql-sql@postgresql.org>; Mon, 26 Jun 2000 00:09:47 -0400 (EDT)
Received: from golem.jpl.nasa.gov (hectic-2 [128.149.68.204])
	by huey.jpl.nasa.gov (8.8.8+Sun/8.8.8) with ESMTP id VAA23345;
	Sun, 25 Jun 2000 21:09:43 -0700 (PDT)
Received: from alumni.caltech.edu (localhost.localdomain [127.0.0.1])
	by golem.jpl.nasa.gov (Postfix) with ESMTP
	id 76DED6F4F; Mon, 26 Jun 2000 04:11:26 +0000 (UTC)
Message-ID: <3956D7EE.3519A134@alumni.caltech.edu>
Date: Mon, 26 Jun 2000 04:11:26 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
Organization: Yes
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.14-15mdksmp i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Oliver Elphick <olly@lfix.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: JOIN syntax
References: <200006252305.e5PN5we16803@linda.lfix.co.uk>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200006/225

> Looking at SQL92, it seems that I ought to be able to do this:
> SELECT * FROM invoice INNER JOIN
>  (SELECT * FROM invoice_line WHERE lineno > 1)
>  ON invoice.invno = invoice_line.invno;
> ERROR:  parser: parse error at or near "("

Wrong syntax (for Postgres anyway). We don't yet have subselects in the
syntax. Thanks for the example though; it seems like some variant should
be allowed, though perhaps not as written above.

Something like

  select * from invoice inner join
    (select * from invoice_line where lineno > 1)
      as IL (invno, yada)
    on invoice.invno = IL.invno;

seems like it could be legal. Your variant of this may have trouble
hooking up invoice_line.invno after the subselect, since the subselect
result may lose the linkage with the underlying input table.

But, all this is theoretical, since Postgres doesn't yet do the right
thing at all.

                        - Thomas