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
