I was trying a more complex query where the table name isn't a simple table name...
select * from ( select * from addresses join user on user.address_id= addresses.id ) address; but that gives like really strange column names... # sqlite3 output id|name|id:1|address_id|name:1 1|there|1|1|bob # my output [ { id: 1, name: 'there', 'id:1': 1, address_id: 1, 'name:1': 'bob' } ] so if I were to try an do like select * from ( select * from addresses join user on user.address_id=addresses.id ) address join pet on pet.user_id= address.id:1; is a syntax error.... ok... select * from ( select count(*)count,* from addresses join users on users.address_id=addresses.id ) address join pets pet on pet.user_id=address.'id:1'; -- I guess that doesn't really make a 'table' to pull from.... 1,1,there,1,1,bob,1,1,odif Column 0 : table_name:(null) origin_name:(null) table_alias:(null) Column 1 : table_name:addresses origin_name:id table_alias:addresses Column 2 : table_name:addresses origin_name:name table_alias:addresses Column 3 : table_name:users origin_name:id table_alias:users Column 4 : table_name:users origin_name:address_id table_alias:users Column 5 : table_name:users origin_name:name table_alias:users Column 6 : table_name:pets origin_name:id table_alias:pet Column 7 : table_name:pets origin_name:user_id table_alias:pet Column 8 : table_name:pets origin_name:name table_alias:pet [ { addresses: { id: 1, name: 'there' }, users: { 'id:1': 1, address_id: 1, 'name:1': 'bob' }, pet: { id: 1, user_id: 1, name: 'odif' }, count: 1, id: [ 1, 1, addresses: 1, pet: 1 ], name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ] } ] On Thu, Aug 15, 2019 at 11:51 AM J Decker <d3c...@gmail.com> wrote: > I would really like to get the table alias specified in the query, with an > unpatched version of Sqlite. > > > So here's a script... > > ------ > .headers on > > create table addresses( id, name ); > create table users( id, address_id, name ); > create table pets(id, user_id, name ); > > insert into addresses(id,name) values ( 1, "there" ); > insert into users(id,address_id,name) values ( 1, 1, "bob" ); > insert into pets(id,user_id,name) values ( 1,1, "odif" ); > > select * from users user join addresses address on address.id=user.address_id > join pets pet on pet.user_id=user.id; > > ------ > This is the output, which you can see in the one case for suer multiple > values come back that are 'id' > > id|address_id|name|id|name|id|user_id|name > 1|1|bob|1|there|1|1|odif > > Sqlite command line tool doesn't really provide all of the information > available though... > > --- > This is the returns of > sqlite3_column_table_name > sqlite3_column_origin_name > , and the function that doesn't exist yet : sqlite3_column_table_alias > > data : 1,1,bob,1,there,1,1,odif > Column 0 : table_name:users origin_name:id table_alias:user > Column 1 : table_name:users origin_name:address_id table_alias:user > Column 2 : table_name:users origin_name:name table_alias:user > Column 3 : table_name:addresses origin_name:id table_alias:address > Column 4 : table_name:addresses origin_name:name table_alias:address > Column 5 : table_name:pets origin_name:id table_alias:pet > Column 6 : table_name:pets origin_name:user_id table_alias:pet > Column 7 : table_name:pets origin_name:name table_alias:pet > > > Desired output: > [ > { > user: { id: 1, address_id: 1, name: 'bob' }, > address: { id: 1, name: 'there' }, > pet: { id: 1, user_id: 1, name: 'odif' }, > id: [ 1, 1, 1, user: 1, address: 1, pet: 1 ], > name: [ > 'bob', > 'there', > 'odif', > user: 'bob', > address: 'there', > pet: 'odif' > ] > } > ] > > // in the resulting object returned to JS, I keep column names that are > duplicated as both an array... > id[0], id[1], id[2] but also related to the table (alias) they come > from. id.name, id.pets, id.address... but conversely by table (alias) > name... name.id, address.id, pets.id > > ----------- > > I would really like to get the table alias specified in the query, with an > unpatched version of Sqlite. > > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users