One more example for the road...
create table fruit ( fruit_id,name)
create table color ( color_id,name)
create table fruit_color ( fruit_id,color_id )
insert into fruit (fruit_id,name) values (1,'apple'),(2,'orange'),(3,'b
anana')"
insert into color (color_id,name) values (1,'red'),(2,'orange'),(3,'yel
low')"
insert into fruit_color (fruit_id,color_id) values (1,1),(2,2),(3,3)"
select 1,1,fruit.*,color.* from fruit join fruit_color on
fruit_color.fruit_id=fruit.fruit_id join color on
fruit_color.color_id=color.color_id
(If the table has columns that overlap)
[ { '1': [ 1, 1 ],
fruit: { fruit_id: 1 },
color: { color_id: 1 },
fruit_id: 1,
name: [ 'apple', 'red', fruit: 'apple', color: 'red' ],
color_id: 1 },
{ '1': [ 1, 1 ],
fruit: { fruit_id: 2 },
color: { color_id: 2 },
fruit_id: 2,
name: [ 'orange', 'orange', fruit: 'orange', color: 'orange' ],
color_id: 2 },
{ '1': [ 1, 1 ],
fruit: { fruit_id: 3 },
color: { color_id: 3 },
fruit_id: 3,
name: [ 'banana', 'yellow', fruit: 'banana', color: 'yellow' ],
color_id: 3 } ]
can be accessed with
result[n].fruit.name
result[n].name.fruit
result[n].name[0]
constants and expressions get put into the base object (unless something
like `as tabname.newcol` can override it? If a column name overlaps a
table name, <strike>it can orphan the table object and replace with the row
value.</strike> throws an exception and gives up. (IE
`select fruit.name as c,fruit.*,c.* from fruit join fruit_color
USING(fruit_id) join color as c USING(color_id)` ).
wouldn't make that the production version of data to rely on... and
wouldn't want to query 1M records with triplicated values :)
On Mon, Jan 15, 2018 at 7:07 AM, J Decker <d3c...@gmail.com> wrote:
>
>
> On Mon, Jan 15, 2018 at 7:05 AM, J Decker <d3c...@gmail.com> wrote:
>
>>
>>
>> On Mon, Jan 15, 2018 at 5:14 AM, Clemens Ladisch <clem...@ladisch.de>
>> wrote:
>>
>>> J Decker wrote:
>>> > What is the expected output?
>>>
>>> And just out of curiosity: what should the table name be for these
>>> columns?
>>>
>>> SELECT articles.gmt_deleted+tags.type, 42, id FROM articles JOIN tags
>>> USING (id);
>>>
>>>
>> table name wouldn't matter.
>>
>> create table articles ( id, content,gmt_deleted ) " );
>> create table tags ( id,type, text ) " );
>> insert into articles (id,content,gmt_deleted) values ( 1, 'hello
>> world','X')" );
>> insert into tags (id,type,text) values ( 1, 'text', 'MOTD')" );
>> SELECT articles.gmt_deleted+tags.type, 42, articles.id FROM articles
>> JOIN tags on articles.id=tags.id;" ) );
>> [ { '42': 42, 'articles.gmt_deleted+tags.type': 0, id: 1 } ]
>>
>>
> SELECT * FROM articles JOIN tags USING(id)
> [ { id: 1,
> content: 'hello world',
> gmt_deleted: 'X',
> type: 'text',
> text: 'MOTD' } ]
>
>>
>>> Regards,
>>> Clemens
>>> _______________________________________________
>>> 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
