Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[TAUZIN Mathieu]

Hi,

You're wrong.
I think I've found the bug. 
It is a parser issue.

According to their definition 
(http://sqlite.org/syntaxdiagrams.html#single-source) , Join sources (named 
single-source) are either : 
* a table or view with an optional alias and/or with an optional index
* a sub query with an optional alias
* a sub join (with no alias)

In SQLite parser.y source code we can find on line 496 the grammar rule 
handling those three cases (in the same order)


...
seltablist(A) ::= stl_prefix(X) nm(Y) dbnm(D) as(Z) indexed_opt(I) on_opt(N) 
using_opt(U). {
  A = sqlite3SrcListAppendFromTerm(pParse,X0,N,U);
  sqlite3SrcListIndexedBy(pParse, A, );
}

seltablist(A) ::= stl_prefix(X) LP select(S) RP
as(Z) on_opt(N) using_opt(U). {
A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,,S,N,U);
  }

seltablist(A) ::= stl_prefix(X) LP seltablist(F) RP
as(Z) on_opt(N) using_opt(U). {
if( X==0 && Z.n==0 && N==0 && U==0 ){
  A = F;
}else{
  Select *pSubquery;
  sqlite3SrcListShiftJoinType(F);
  pSubquery = sqlite3SelectNew(pParse,0,F,0,0,0,0,0,0,0);
  A = sqlite3SrcListAppendFromTerm(pParse,X,0,0,,pSubquery,N,U);
}
  }
...


Case 1 and 2 are handled properly but as you can see the third definition (wich 
should deal with sub joins) contains mistakes :
#1 : It allows an as clause after the parenthesis
#2 : on the right of a join operator (else { ... }) it generates a 
subquery instead of merging F (which is a seltabList, not a sub query) with X 
into A.

Do you still think there is no issue here ?

I wish I could propose a fix but I have no skills in C/yacc.

Hope this will help anyway.

Thanks

-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
De la part de Nico Williams
Envoyé : lundi 19 mars 2012 16:10
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On Mon, Mar 19, 2012 at 10:02 AM, TAUZIN Mathieu <mtau...@cegid.fr> wrote:
> Thanks,
>
> This syntax works but it is not documented... it looks like a short hand for 
> a subquery, interesting !.

Join sources are like sub-queries.  Look at the syntax.

A sub-select specified in the join-source following the FROM clause in a simple 
SELECT statement is handled as if it was a table containing the data returned 
by executing the sub-select statement.  The docs could perhaps be clearer about 
this.  This sentence:

"A sub-select specified in the join-source following the FROM clause in a 
simple SELECT statement is handled as if it was a table containing the data 
returned by executing the sub-select statement. "

does hint at this, but maybe that's just because I think of  as 
a sort of sub-select.

Nico
--
___
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

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Ryan Johnson]

On 19/03/2012 12:07 PM, TAUZIN Mathieu wrote:

Thanks for your support !



SQL Ansi  (and every major DB 
SqlServer, 
Oracle) 
supports this syntax as described in SQLite documentation.



The parenthesis are here to enforce the priority of the joins.
FYI, sqlite3 does not respect parentheses as a way of enforcing join 
orders. If you want to bypass the optimizer, the accepted way (as with 
postgres, I believe) is to use a CROSS JOIN rather than the default 
[INNER] JOIN. You may want to test whether the above-mentioned engines 
actually respect parentheses for join ordering.


Whether the parenthesis as used should or should not introduce a scope, 
I'm not prepared to make any claims about...


Ryan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[TAUZIN Mathieu]

Thanks for your support !



SQL Ansi<http://savage.net.au/SQL/sql-99.bnf.html#qualified%20join> (and every 
major DB 
SqlServer<http://msdn.microsoft.com/en-US/library/ms177634(v=sql.90).aspx>, 
Oracle<http://docs.oracle.com/cd/B13789_01/server.101/b10759/statements_10002.htm>)
 supports this syntax as described in SQLite documentation.



The parenthesis are here to enforce the priority of the joins.



If the subjoined objects are no longer accessible outside the parenthesis what 
would be the interest of such a syntax ?

Therefore this query works fine :


SELECT  Orders.OrderID
FROM (Customers
  INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID )
  INNER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
WHERE 'ALFKI' = Customers.CustomerID



Whereas that one doesn't :


SELECT  Orders.OrderID
FROM Customers
  INNER JOIN
(Orders
  INNER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID)
  ON Customers.CustomerID = Orders.CustomerID
WHERE 'ALFKI' = Customers.CustomerID



So, sub-joins on the left of the join-type are OK

but sub-joins on the right of the joint-type are NOT OK.



It seems like a bug to me.



Mathieu



-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
De la part de Jay A. Kreibich
Envoyé : lundi 19 mars 2012 16:26
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'



On Mon, Mar 19, 2012 at 12:03:44PM +, 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<mailto: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

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Jay A. Kreibich]

On Mon, Mar 19, 2012 at 12:03:44PM +, 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

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Nico Williams]

On Mon, Mar 19, 2012 at 10:02 AM, TAUZIN Mathieu  wrote:
> Thanks,
>
> This syntax works but it is not documented... it looks like a short hand for 
> a subquery, interesting !.

Join sources are like sub-queries.  Look at the syntax.

A sub-select specified in the join-source following the FROM clause in
a simple SELECT statement is handled as if it was a table containing
the data returned by executing the sub-select statement.  The docs
could perhaps be clearer about this.  This sentence:

"A sub-select specified in the join-source following the FROM clause
in a simple SELECT statement is handled as if it was a table
containing the data returned by executing the sub-select statement. "

does hint at this, but maybe that's just because I think of
 as a sort of sub-select.

Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[TAUZIN Mathieu]

Thanks,

This syntax works but it is not documented... it looks like a short hand for a 
subquery, interesting !.

Mathieu

-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
De la part de Simon Davies
Envoyé : lundi 19 mars 2012 14:24
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On 19 March 2012 13:05, Pavel Ivanov <paiva...@gmail.com> wrote:
>> 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

Maybe what the application should be doing:

SELECT subjoin.OrderID
FROM Customers
INNER JOIN
(Orders
LEFT OUTER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
) AS subjoin
ON Customers.CustomerID = subjoin.CustomerID WHERE 'ALFKI' = 
Customers.CustomerID

Regards,
Simon
___
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

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Simon Davies]

On 19 March 2012 13:05, Pavel Ivanov  wrote:
>> 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

Maybe what the application should be doing:

SELECT subjoin.OrderID
FROM Customers
INNER JOIN
(Orders
LEFT OUTER JOIN InternationalOrders
ON Orders.OrderID = InternationalOrders.OrderID
) AS subjoin
ON Customers.CustomerID = subjoin.CustomerID
WHERE 'ALFKI' = Customers.CustomerID

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Simon Slavin]

On 19 Mar 2012, at 12:51pm, TAUZIN Mathieu  wrote:

> Thanks for your response but my intent was to give rise to either an bug on 
> SQLite engine or an error (or maybe lack of precision) in the documentation.

SQLite is fine.  The documentation is accurate about what SQLite does.

> The sample I gave is a simplified version of the real query which is built by 
> a tool so I have not the choice on the form.

If your tool is intended for SQLite, and builds this form for SQLite, then 
there is a bug in your tool.  You can submit this bug with the authors of that 
tool.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Pavel Ivanov]

> 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  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

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[TAUZIN Mathieu]

Thanks for your response but my intent was to give rise to either an bug on 
SQLite engine or an error (or maybe lack of precision) in the documentation.

The sample I gave is a simplified version of the real query which is built by a 
tool so I have not the choice on the form.

Mathieu.

-Message d'origine-
De : sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
De la part de Simon Slavin
Envoyé : lundi 19 mars 2012 13:20
À : General Discussion of SQLite Database
Objet : Re: [sqlite] Join-source issue when using sub '(' join-source ')'


On 19 Mar 2012, at 12:03pm, TAUZIN Mathieu <mtau...@cegid.fr> wrote:

> 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

This form should work fine in SQLite and is the expected way to do JOINs to 
many files.  It is just as efficient as subjoins in SQLite: the engine ends up 
doing no more work.  And it has the advantage that it works in many different 
versions of SQL since it's part of the SQL definition.  Unless you have a 
particular reason for wanting to avoid this form, I'd say go ahead and use it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

>>Original message :
>>
>>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

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

[Simon Slavin]

On 19 Mar 2012, at 12:03pm, TAUZIN Mathieu  wrote:

> 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

This form should work fine in SQLite and is the expected way to do JOINs to 
many files.  It is just as efficient as subjoins in SQLite: the engine ends up 
doing no more work.  And it has the advantage that it works in many different 
versions of SQL since it's part of the SQL definition.  Unless you have a 
particular reason for wanting to avoid this form, I'd say go ahead and use it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Join-source issue when using sub '(' join-source ')'

[TAUZIN Mathieu]

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