Re: [sqlite] Multiple inner join confusion

[Rob Duncan]

Thanks to Richard for responding—I will do as he suggests.  I am still 
curious about the cause of the error, though.  After sleeping on it my 
hypothesis is that the temporary table generated by the first of my 
inner joins has columns with names "aa.i" and "aa.j", so the using 
clause of the second inner join fails (because it requires the 
existence of a column named "j").  Am I on the right track?

Secondly, does the select suggested below do the full cartesian product 
of the tables before extracting the rows that match the condition?  If 
the tables are large do I get better performance by explicitly using a 
series of joins instead?  In that case would it be better to code it 
something like this:

select * from aa inner join bb on (aa.i=bb.i) inner join cc on 
(aa.j=cc.j);

Thirdly, what is the explanation for the different results from these 
similar selects?

select * from aa inner join bb on (aa.i=bb.i);
aa.i  aa.j  bb.i
    --
1 2 1
5 6 5
select * from aa inner join bb using (i);
aa.i  aa.j
  
1 2
5 6
Thanks,

Rob.

On Apr 12, 2004, at 5:00 AM, D. Richard Hipp wrote:

Rob Duncan wrote:
I'm a novice SQL user, and I'm confused by an apparently arbitrary 
limitation on multiple inner joins. select * from aa inner join bb 
using (i) inner join cc using (j);
SQL error: cannot join using column j - column not present in both 
tables
I suggest you work around the problem by coding the select like this:

   select * from aa, bb, cc where aa.i=bb.i and cc.j=aa.j;


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: [sqlite] Multiple inner join confusion

[Cronos]

I am also having difficulty getting to grips with the (awkward) join
notation. In 3.0 how about support for Oracle type joins with (+) which
seems far simpler to me :)

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: 12 April 2004 13:00
Cc: [EMAIL PROTECTED]
Subject: Re: [sqlite] Multiple inner join confusion


Rob Duncan wrote:
> I'm a novice SQL user, and I'm confused by an apparently arbitrary
> limitation on multiple inner joins.
>
> select * from aa inner join bb using (i) inner join cc using (j);
> SQL error: cannot join using column j - column not present in both tables
>

I suggest you work around the problem by coding the select like this:

select * from aa, bb, cc where aa.i=bb.i and cc.j=aa.j;


--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: [sqlite] Multiple inner join confusion

[D. Richard Hipp]

Rob Duncan wrote:
I'm a novice SQL user, and I'm confused by an apparently arbitrary 
limitation on multiple inner joins. 

select * from aa inner join bb using (i) inner join cc using (j);
SQL error: cannot join using column j - column not present in both tables
I suggest you work around the problem by coding the select like this:

   select * from aa, bb, cc where aa.i=bb.i and cc.j=aa.j;

--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]