The query does not work because it is defective. The column name pragma's only affect the column names that your program sees, not the names used internally in the SQL statement. It does not affect the identifier use internally. If your internal identifier use is defective, then it is defective and only fixing the defect will fix it. In this particular case it can ONLY be fixed by specifying appropriate column names using AS. In other words DO NOT use * unless you are absolutely certain that the column names are unique. This is not a "personal peeve", this is how SQL works. Do not use *. Period. End of story. This is almost always one of the very first things one learns when using SQL. This is because if something gets changed then the meaning of * gets changed. If the query optimizer decides to do things differently (in a different order), the results will be different.
Using * means that you are relying on happenstance rather than actually writing what you mean. 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; The subquery "select count(*) count,* from addresses join users on users.address_id=addresses.id" has duplicated column names, and there is nothing that you can do about this except use AS to give specific distinct names to the columns. You then alias this as a tableform entity called address. There are two id columns in address, and there is no way that you can specify in the outer query which one you want other than by giving them unique names in the inner query by listing explicit columns and giving them unique aliases using AS. Like I said, if you care about column names then give the columns names, and if you need to distinguish between two columns with the same name, give them different aliases. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of J Decker >Sent: Thursday, 15 August, 2019 14:37 >To: SQLite mailing list >Subject: Re: [sqlite] Request to get alias of table > >> >> >> >> 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 >> >> >>> console.log( db.do( "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.user.id';" ) ); > > >13:34:27.325|212800001160~sql_module.cc(571):Do >Command[04F1ECF4:test.db]: >select count(*)count,* from ( select count(*)count,* from addresses >join >users on users.address_id=addres >ses.id ) address join pets pet on pet.user_id='address.user.id'; >13:34:27.326|212800001160~sack.cc(97503):Sqlite3 Err: (284) automatic >index >on users(address_id) >13:34:27.326|212800001160~sack.cc(104891):0 >13:34:27.326|212800001160~sack.cc(101458):Column 0 : >table_name:(null) >origin_name:(null) table_alias:(null) >13:34:27.327|212800001160~sack.cc(101458):Column 1 : >table_name:(null) >origin_name:(null) table_alias:(null) >13:34:27.327|212800001160~sack.cc(101458):Column 2 : >table_name:addresses >origin_name:id table_alias:addresses >13:34:27.327|212800001160~sack.cc(101458):Column 3 : >table_name:addresses >origin_name:name table_alias:addresses >13:34:27.327|212800001160~sack.cc(101458):Column 4 : table_name:users >origin_name:id table_alias:users >13:34:27.327|212800001160~sack.cc(101458):Column 5 : table_name:users >origin_name:address_id table_alias:users >13:34:27.328|212800001160~sack.cc(101458):Column 6 : table_name:users >origin_name:name table_alias:users >13:34:27.328|212800001160~sack.cc(101458):Column 7 : table_name:pets >origin_name:id table_alias:pet >13:34:27.328|212800001160~sack.cc(101458):Column 8 : table_name:pets >origin_name:user_id table_alias:pet >13:34:27.328|212800001160~sack.cc(101458):Column 9 : table_name:pets >origin_name:name table_alias:pet >13:34:27.328|212800001160~sack.cc(104418):no data >[ > { > count: 0, > 'address.count': null, > 'address.addresses.id': null, > 'address.addresses.name': null, > 'address.users.id': null, > 'address.users.address_id': null, > 'address.users.name': null, > 'pet.id': null, > 'pet.user_id': null, > 'pet.name': null > } >] > >Hmm... I don't get any values that way, I wonder what happens? > >I suppose parsing that might be an option; not sure I can guarantee >databases will always pragma longname.... >_______________________________________________ >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
