Обсуждение: Nested JOINs
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
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
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 ONc.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 informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"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>
<derivedcolumn list> <right paren> ] ] | <derived table> [ AS ] <correlation name> [
<leftparen> <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
I think Tom was right and the problem with nested joins is caused by the outdated installation of my PostgreSQL. So, I am trying to upgrade to 7.1.2 and when I use: pg_dumpall -o > file.bac I get an error saying: dumpRules(): SELECT failed for table setmedias. Explanation from backend: 'ERROR: cache lookup of attribute 5 in relation 23945 failed" As I understand setmedias table corresponds to relation 23945, but I dropped it a long time ago. How should remove this relation pointer? thanks, Oleg Tom Lane wrote: > "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