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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users