pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNested JOINs
4+ messages / 3 participants
[nested] [flat]
* Nested JOINs
@ 2001-08-16 20:28 Oleg Lebedev <olebedev@waterford.org>
2001-08-16 21:42 ` Re: Nested JOINs Tom Lane <tgl@sss.pgh.pa.us>
2001-08-16 21:44 ` Re: Nested JOINs Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 4+ messages in thread
From: Oleg Lebedev @ 2001-08-16 20:28 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am trying to execute an SQL query that contains nested joins, but I
get parser error for some reason.
Below is the query I am trying to execute and the error I am getting:
SELECT media
FROM (dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c
INNER JOIN dtcol d ON (c.dtcol = d.objectid)
psql: ERROR: parser: parse error at or near "inner"
Note that the following query executes with no errors:
SELECT media
FROM dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)
INNER JOIN dtcol d ON (b.dtcol = d.objectid)
What am I doing wrong?
Please help me get it to work.
Thanks
Oleg
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested JOINs
2001-08-16 20:28 Nested JOINs Oleg Lebedev <olebedev@waterford.org>
@ 2001-08-16 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-08-16 21:42 UTC (permalink / raw)
To: Oleg Lebedev <olebedev@waterford.org>; +Cc: pgsql-sql
Oleg Lebedev <olebedev@waterford.org> writes:
> What am I doing wrong?
Using 7.0, perhaps? The query parses fine for me in 7.1.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested JOINs
2001-08-16 20:28 Nested JOINs Oleg Lebedev <olebedev@waterford.org>
@ 2001-08-16 21:44 ` Josh Berkus <josh@agliodbs.com>
2001-08-16 22:12 ` Re: Nested JOINs Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Josh Berkus @ 2001-08-16 21:44 UTC (permalink / raw)
To: Oleg Lebedev <olebedev@waterford.org>; pgsql-sql
Oleg,
> Below is the query I am trying to execute and the error I am getting:
> SELECT media
> FROM (dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c
> INNER JOIN dtcol d ON (c.dtcol = d.objectid)
First, the "INNER" keyword is not required on Postgres; you may omit it
with impunity. (If you are doing this on MS SQL Server or MS Access and
have joined this list by mistake, our advice won't be very useful to
you).
Second, you can't alias a JOINed set of tables; you may alias a
subselect, or alias a table:
FROM dtrow b ... is legal
FROM (SELECT * FROM dtrow) b ... is also legal
FROM (dtrow JOIN dtrowmedia ON dtrow.objectid = dtrowmedia.dtrow) b
... is not legal.
Thus, the query above is best expressed simply as:
SELECT media
FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid
JOIN dtcol d ON b.dtcol = d.objectid;
If you actually did need to put some extra criteria into the first join,
then you would use a subselect:
SELECT media
FROM (SELECT * FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid
WHERE dtrow > 3000) c
JOIN dtcol d ON c.dtcol = d.objectid;
But keep in mind in this case that you cannot reference a. or b. in the
SELECT list at the top, just c. because a. and b. exist only in the
subselect.
Now, go out and buy a copy of "SQL for Smarties". You'll be glad you
did.
-Josh Berkus
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Nested JOINs
2001-08-16 20:28 Nested JOINs Oleg Lebedev <olebedev@waterford.org>
2001-08-16 21:44 ` Re: Nested JOINs Josh Berkus <josh@agliodbs.com>
@ 2001-08-16 22:12 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-08-16 22:12 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Oleg Lebedev <olebedev@waterford.org>; pgsql-sql
"Josh Berkus" <josh@agliodbs.com> writes:
> Second, you can't alias a JOINed set of tables;
Actually you can, according to my reading of SQL92:
<table reference> ::=
<table name> [ [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ] ]
| <derived table> [ AS ] <correlation name>
[ <left paren> <derived column list> <right paren> ]
| <joined table>
<derived table> ::= <table subquery>
<table subquery> ::= <subquery>
<subquery> ::= <left paren> <query expression> <right paren>
<query expression> ::=
<non-join query expression>
| <joined table>
<joined table> ::=
<cross join>
| <qualified join>
| <left paren> <joined table> <right paren>
<qualified join> ::=
<table reference> [ NATURAL ] [ <join type> ] JOIN
<table reference> [ <join specification> ]
So a parenthesized <qualified join> is a <derived table> and may
be followed by [ AS ] <correlation name>.
7.1 gets this right. 7.0's very preliminary implementation of JOIN
syntax did not. (It had a number of other bugs too, IIRC.)
The spec's grammar is horribly badly written: it's ambiguous whether the
parentheses should be parsed as part of a second-level <joined table>
which would imply that a correlation name would NOT be accepted.
It took a fair amount of work to derive a grammar that was unambiguous
and still accepted everything...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-08-16 22:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-16 20:28 Nested JOINs Oleg Lebedev <olebedev@waterford.org>
2001-08-16 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-08-16 21:44 ` Josh Berkus <josh@agliodbs.com>
2001-08-16 22:12 ` Tom Lane <tgl@sss.pgh.pa.us>
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