Thanks for your support !
SQL Ansi<http://savage.net.au/SQL/sql-99.bnf.html#qualified%20join> (and every major DB SqlServer<http://msdn.microsoft.com/en-US/library/ms177634(v=sql.90).aspx>, Oracle<http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_10002.htm>) supports this syntax as described in SQLite documentation. The parenthesis are here to enforce the priority of the joins. If the subjoined objects are no longer accessible outside the parenthesis what would be the interest of such a syntax ? Therefore this query works fine : SELECT Orders.OrderID FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID ) INNER JOIN InternationalOrders ON Orders.OrderID = InternationalOrders.OrderID WHERE 'ALFKI' = Customers.CustomerID Whereas that one doesn't : SELECT Orders.OrderID FROM Customers INNER JOIN (Orders INNER JOIN InternationalOrders ON Orders.OrderID = InternationalOrders.OrderID) ON Customers.CustomerID = Orders.CustomerID WHERE 'ALFKI' = Customers.CustomerID So, sub-joins on the left of the join-type are OK but sub-joins on the right of the joint-type are NOT OK. It seems like a bug to me. Mathieu -----Message d'origine----- De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] De la part de Jay A. Kreibich Envoyé : lundi 19 mars 2012 16:26 À : General Discussion of SQLite Database Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')' On Mon, Mar 19, 2012 at 12:03:44PM +0000, TAUZIN Mathieu scratched on the wall: > Hi, > > According to the documentation on SELECT statements > http://www.sqlite.org/lang_select.html > It seems possible to write join chains as A join (B join C). > (using a '(' join-source ')' single-source ) > > But on the well known NorthwindEF database this query ... > > SELECT Orders.OrderID > FROM Customers > INNER JOIN > (Orders > LEFT OUTER JOIN InternationalOrders > ON Orders.OrderID = InternationalOrders.OrderID > ) > ON Customers.CustomerID = Orders.CustomerID WHERE 'ALFKI' = > Customers.CustomerID > > ... raises an error : > no such column: Orders.OrderID This does appear to be a bug. You can get around this using an AS clause to name the sub-expression: SELECT OrdInt.OrderID FROM Customers INNER JOIN ( Orders LEFT OUTER JOIN InternationalOrders ON Orders.OrderID = InternationalOrders.OrderID ) AS OrdInt ON Customers.CustomerID = OrdInt.CustomerID WHERE 'ALFKI' = OrdInt.CustomerID The thing is, you're not supposed to need to name a sub-expression. In fact, according the the "single-source" syntax diagram, naming a sub-expression (via AS) isn't even allowed. To be clear, a sub-*select* that is used as a source can be (and, in fact, must be) named to access it outside of the sub-select, but a sub-*expression*-- where the parenthesis only serve to enforce order of operations-- should expose the contained tables, just as if the parenthesis were not there. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
