[sqlite] Columns being dropped in query result in Mac OS X Terminal
I am working in SQLite 3.1.3 on Mac OS X 10.4.4. I keep getting the following error and am wondering why and what I can do about it? When I query mukey, cokey, and comppct_r independently I get the correct result. However, when I query for all three the columns are misaligned and the first column gets dropped. Is this a problem with the interface program in OS X's Terminal, or with SQLite itself? mukey is numeric, comppct_r is numeric, cokey is text. This seems to be messing up a join. sqlite> select mukey from component limit 5; mukey -- 456557 456557 456557 456557 456557 sqlite> select cokey from component limit 5; cokey -- 456557:612401 456557:612402 456557:612403 456557:612404 456557:612405 sqlite> select comppct_r from component limit 5; comppct_r -- 85 3 2 5 5 sqlite> select mukey, cokey from component limit 5; mukey cokey -- -- 456557 456557:612401 456557 456557:612402 456557 456557:612403 456557 456557:612404 456557 456557:612405 sqlite> select mukey, cokey, comppct_r from component limit 5; mukey cokey comppct_r -- -- -- 85456557:612401 3 456557:612402 2 456557:612403 5 456557:612404 5 456557:612405 sqlite> select cokey, comppct_r, mukey from component limit 5; cokey comppct_r mukey -- -- -- 85 456557 3 456557 2 456557 5 456557 5 456557 But wait! It works as the following order! What's happening? sqlite> select comppct_r, mukey, cokey from component limit 5; comppct_r mukey cokey -- -- -- 85 456557 456557:612401 3 456557 456557:612402 2 456557 456557:612403 5 456557 456557:612404 5 456557 456557:612405 And it seems dependent on the .mode: Column mode: sqlite> select cokey, comppct_r from component limit 5; cokey comppct_r -- -- 851 3 2 2 3 5 4 5 5 List mode: sqlite> select cokey, comppct_r from component limit 5; cokey|comppct_r |85557:612401 |36557:612402 |26557:612403 |56557:612404 |56557:612405
Re: [sqlite] How to install SQLite for use in XCode (MacOSX) ?
Or you can just include the source in your XCode project, as done with the QuickLite wrapper (check http://www.webbotech.com/ for an example). Philip On Oct 2, 2004, at 3:16 PM, b.bum wrote: On Oct 2, 2004, at 10:29 AM, Eric Morand wrote: I'm currently on the process to use an SQLite database on my new XCode project, for MacOSX Panther. But I have no idea on how I should install the database framework... Can someone explain me the entire process ? Sure. First, decide if you want to statically or dynamically link SQLite. If you are planning on distributing your app to others that may not have SQLite installed, then I would suggest that you should statically link SQLite. Assuming you want to do so: - download SQLite 3.0.7 from www.sqlite.org - configure and build it (assumes you have dev tools): cd sqlite-3.0.7/ ./configure --disable-shared --enable-static sudo make install Then, in your Xcode project: - add /usr/local/lib/libsqlite3.a to your project. Use an absolute path reference and do not copy the library into your project (though, frankly, you could copy it -- it won't hurt anything and it'll make your project source dir more portable) - to include the sqlite3 header: #include That's all you need to do. b.bum
[sqlite] Natural join behavior includes primary key field?
Is this correct behavior for natural join?
I have 2 tables:
CREATE TABLE table_1 (rowid integer primary key, name string, join_code
string)
CREATE TABLE table_2 (rowid integer primary key, join_code string, type
string)
insert the following values:
insert into table_1 (name, join_code) values ('n101', 'one');
insert into table_1 (name, join_code) values ('n102', 'two');
insert into table_1 (name, join_code) values ('n103', 'one');
insert into table_1 (name, join_code) values ('n104', 'one');
insert into table_1 (name, join_code) values ('n105', 'two');
insert into table_2 (join_code, type) values ('one', 'house');
insert into table_2 (join_code, type) values ('one', 'apartment');
Then perform the following queries:
select * from table_1 natural join table_2;
table_1. rowid, table_1.name, table_1.join_code, table_2. type
1, n101, one, house
2, n102, two, apartment
select * from table_1 inner join table_2 using (join_code);
table_1. rowid, table_1.name, table_1.join_code, table_2.rowid,
table_2. type
1, n101, one, 1, house
2, n102, two, 2, apartment
3, n103, one, 1, house
4, n104, one, 1, house
5, n105, two, 2 apartment
It is obvious that SQLite is performing the natural join on both the
rowid and the join_code fields. Is this the correct behavior? I would
have thought it should ignore the primary key field when performing a
natural join.
Philip
[sqlite] left outer join returns duplicates
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.
