> 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 ) ... > 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 ?
So there's no problem in documentation and you can indeed write join chains using parenthesis. Also there's no bug in SQLite because (according to documentation) join-source with parenthesis is considered a single-source and as a consequence you can't link to some details of this single-source outside of parenthesis. Maybe MS SQL Server and Oracle provide an extension to do that, but SQLite doesn't. So we can't confirm a bug and you didn't miss anything. You can file a bug with the application generating your original query. Pavel On Mon, Mar 19, 2012 at 8:03 AM, TAUZIN Mathieu <mtau...@cegid.fr> wrote: > 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 > 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
