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):
>
sqlite> .headers on
> sqlite> create table addresses( id, name );
> sqlite> create table users( id, address_id, name );
> sqlite> create table pets(id, user_id, name );
> sqlite>
> sqlite> insert into addresses(id,name) values ( 1, 'there' );
> sqlite> insert into users(id,address_id,name) values ( 1, 1, 'bob' );
> sqlite> insert into pets(id,user_id,name) values ( 1,1, 'odif' );
> sqlite>
> sqlite> 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.
>
>
sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address ;
address.count|address.addresses.id|address.addresses.name|address.users.id
|address.users.address_id|address.users.name
1|1|there|1|1|bob
sqlite> select * from ( select count(*)count,* from addresses join users on
users.address_id=addresses.id ) address join pets pet on pet.user_id=
address.users.id;
Error: no such column: address.users.id
How do I fix that query to work then?
Hmm, I don't really like this as the output though... I'd rather have the
individual names without parsing...
select * from users user join addresses address on address.id=user.address_id
join pets pet on pet.user_id=user.id;
[
{
'user.id': 1,
'user.address_id': 1,
'user.name': 'bob',
'address.id': 1,
'address.name': 'there',
'pet.id': 1,
'pet.user_id': 1,
'pet.name': 'odif'
}
]
select * from ( select * from addresses join users on users.address_id=
addresses.id ) address
[
{
'address.addresses.id': 1,
'address.addresses.name': 'there',
'address.users.id': 1,
'address.users.address_id': 1,
'address.users.name': 'bob'
}
]
Intersting, origin_name is still the short name....
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
--
> 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
