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
