Using AS to name columns means NOT using *. One only uses * when one does not
care about column names (or the ordering of the result columns).
select address.id AS address_id,
address.name AS address_name,
user.id AS user_id,
user.address_id AS user_address_id,
user.name AS user_name,
pet.id AS pet_id,
pet.user_id AS pet_user_id,
pet.name AS pet_name
from users AS user
join addresses AS address
on address.id == user.address_id
join pets AS pet
on pet.user_id == user.id;
demonstrates how to specify column names.
import apsw
db = apsw.Connection('')
db.execute('''
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' );
''')
cr1 = db.execute('''
select *
from users user
join addresses address
on address.id=user.address_id
join pets pet
on pet.user_id=user.id;')
cr2 = db.execute('''
select address.id AS address_id,
address.name AS address_name,
user.id AS user_id,
user.address_id AS user_address_id,
user.name AS user_name,
pet.id AS pet_id,
pet.user_id AS pet_user_id,
pet.name AS pet_name
from users AS user
join addresses AS address
on address.id == user.address_id
join pets AS pet
on pet.user_id == user.id;
''')
cr1.fetchone()
Row(id=1, address_id=1, name=u'bob', id_1=1, name_1=u'there', id_1=1,
user_id=1, name_1=u'odif')
cr2.fetchone()
Row(address_id=1, address_name=u'there', user_id=1, user_address_id=1,
user_name=u'bob', pet_id=1, pet_user_id=1, pet_name=u'odif')
*Note that I have modified apsw from the default by having execute() be a
method of the connection, and having data returned as a Row object, via a
python wrapper. It used to be able to handle the full_column_names but I got
rid of that since it required modifying the apsw code itself to return
additional column metadata.
--
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
