Re: [sqlite] left outer join returns duplicates

[D. Richard Hipp]

Philip Riggs wrote:
I have the two following tables for testing:
Test1)  rowid | class | join_value
1 | 'one' | 1
2 | 'two' | 2
Test2)  rowid | type | join_value
1 | 'number' | 1
2 | 'string' | 2
and peform the following query:
select * from test1 natural join test2
I expect the following:
test1.rowid | test1.class | test1.join_value | test2.type
1 | one | 1 | number
2 | two | 2 | string
but I instead get the following:
test1.rowid | test1.class | test1.join_value | test2.type
1 | one | 1 | number
2 | two | 2 | string
1 | one | 1 | number
2 | two | 2 | string
I did this:
create table test1(class, join_value);
insert into test1 values('one',1);
insert into test1 values('two',2);
create table test2(type, join_value);
insert into test2 values('number',1);
insert into test2 values('string',2);
select * from test1 natural join test2;
And I got this:
one|1|number
two|2|string
Version 3.0.6.  If you are getting something different, please
supply the specifics.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

[sqlite] left outer join returns duplicates

[Philip Riggs]

I have the two following tables for testing:
Test1)  rowid | class | join_value
1 | 'one' | 1
2 | 'two' | 2
Test2)  rowid | type | join_value
1 | 'number' | 1
2 | 'string' | 2
and peform the following query:
select * from test1 natural join test2
I expect the following:
test1.rowid | test1.class | test1.join_value | test2.type
1 | one | 1 | number
2 | two | 2 | string
but I instead get the following:
test1.rowid | test1.class | test1.join_value | test2.type
1 | one | 1 | number
2 | two | 2 | string
1 | one | 1 | number
2 | two | 2 | string
Why are my data duplicating rows? I've been over the SQL statement in 
books and this is the way it demonstrates this query, but the SQLite 
results don't match what the books say to expect. I even tried this:

select test1.class, test2.type from test1 natural join test2 group by 
class, type

But still get duplicates.