Thanks for your response but my intent was to give rise to either an bug on SQLite engine or an error (or maybe lack of precision) in the documentation.
The sample I gave is a simplified version of the real query which is built by a tool so I have not the choice on the form. Mathieu. -----Message d'origine----- De : [email protected] [mailto:[email protected]] De la part de Simon Slavin Envoyé : lundi 19 mars 2012 13:20 À : General Discussion of SQLite Database Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')' On 19 Mar 2012, at 12:03pm, TAUZIN Mathieu <[email protected]> wrote: > Or without subjoin... > SELECT Orders.OrderID > FROM Customers > INNER JOIN Orders > ON Customers.CustomerID = Orders.CustomerID > LEFT OUTER JOIN InternationalOrders > ON Orders.OrderID = InternationalOrders.OrderID WHERE > 'ALFKI' = Customers.CustomerID This form should work fine in SQLite and is the expected way to do JOINs to many files. It is just as efficient as subjoins in SQLite: the engine ends up doing no more work. And it has the advantage that it works in many different versions of SQL since it's part of the SQL definition. Unless you have a particular reason for wanting to avoid this form, I'd say go ahead and use it. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>Original message : >> >>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 >> >>It seems that parsing is ok (no syntax error) but sources in the sub join >>can't be used outside the parenthesis. >> >>Could you confirm this is a bug ? or did I miss something ? >> >>I tried this query on other DB engines (SqlServer and Oracle) and it works >>fine (producing the same execution plan than the equivalent queries below). >> >>I know I could rewrite my example with a sub query ... >>SELECT Useless.OrderID >>FROM Customers >> INNER JOIN ( >> SELECT >> Orders.OrderID as OrderID, >> Orders.CustomerID as CustomerID >> FROM Orders LEFT OUTER JOIN InternationalOrders ON Orders.OrderID >> = InternationalOrders.OrderID >> ) AS Useless >> ON Customers.CustomerID = Useless.CustomerID WHERE 'ALFKI' = >> Customers.CustomerID >> >>Or without subjoin... >>SELECT Orders.OrderID >>FROM Customers >> INNER JOIN Orders >> ON Customers.CustomerID = Orders.CustomerID >> LEFT OUTER JOIN InternationalOrders >> ON Orders.OrderID = InternationalOrders.OrderID WHERE 'ALFKI' = >> Customers.CustomerID >> >>But it illustrates the issue. >> >>Thanks, >> >>Mathieu TAUZIN >> _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
