On Thu, Aug 15, 2019 at 12:33 PM J Decker <d3c...@gmail.com> wrote:
>
>
> On Thu, Aug 15, 2019 at 12:24 PM Keith Medcalf <kmedc...@dessus.com>
> wrote:
>
>> After fixing the errors in the script (strings are quoted with single
>> quotes, not double quotes):
>>
> .headers on
> pragma full_column_names=1;
> pragma short_column_names=0;
> 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;
>
>> id|address_id|name|id|name|id|user_id|name
>> 1|1|bob|1|there|1|1|odif
>> sqlite> pragma full_column_names=1;
>> sqlite> pragma short_column_names=0;
>> sqlite> select * from users user join addresses address on address.id
>> =user.address_id
>> ...> join pets pet on pet.user_id=user.id;
>> user.id|user.address_id|user.name|address.id|address.name|pet.id
>> |pet.user_id|pet.name
>> 1|1|bob|1|there|1|1|odif
>> sqlite>
>>
>> Note that the short_column_names and full_column_names pragma's are
>> deprecated even though highly useful. You have to turn off
>> short_column_names (the default) in order for full_column_names to have any
>> effect.
>>
>> Note that you SHOULD be using AS to name your columns, if you care about
>> column names, and not relying on implementation details.
>>
>> Even if I do use AS, queries could be composed of expressions from other
queries, and eventually what someone makde 'unique' will be the same like
'count(*) AS count' , count, count, count....
select count(*)count,* 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';
1,1,1,there,1,1,bob,1,1,odif
Column 0 : table_name:(null) origin_name:(null) table_alias:(null)
-- I would have expected 'table_name' to be (select
count(*)count,*,fromaddresses.....) and alias to be 'address'
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)
Column 1 : table_name:(null) origin_name:(null) table_alias:(null)
-- I would have expected 'table_name' to be addresses and alias to be
'addresses' /* no alias, I'm not re-testing */
-- I would have expected origin_name to be 'count(*)' and column_name to be
'count' (which it is)
Column 2 : table_name:addresses origin_name:id table_alias:addresses
Column 3 : table_name:addresses origin_name:name table_alias:addresses
Column 4 : table_name:users origin_name:id table_alias:users
Column 5 : table_name:users origin_name:address_id table_alias:users
Column 6 : table_name:users origin_name:name table_alias:users
Column 7 : table_name:pets origin_name:id table_alias:pet
Column 8 : table_name:pets origin_name:user_id table_alias:pet
Column 9 : 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' },
/* I Have an error apparently (or feature), that if there isn't a
table/alias name, I don't split it...
I should at least promote it to an array... */
count: 1,
id: [ 1, 1, addresses: 1, pet: 1 ],
name: [ 'there', 'odif', addresses: 'there', pet: 'odif' ]
}
]
>
>
> --
>> The fact that there's a Highway to Hell but only a Stairway to Heaven
>> says a lot about anticipated traffic volume.
>>
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
