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