I have two tables in sqlite:

CREATE TABLE fruit ('fid' integer, 'name' text);
CREATE TABLE basket ('fid1' integer, 'fid2' integer, 'c1' integer, 'c2' integer);

basket is supposed to have count c1 of fruit fid1 and c2 of fruit fid2

I created a view fruitbasket;

create view fruitbasket as select * from basket inner join fruit a on a.fid=basket.fid1 inner join fruit b on b.fid=basket.fid2;

it works (almost) as expected.

When I type

pragma table_info(fruitbasket);

I get the following output

0|fid1|integer|0||0
1|fid2|integer|0||0
2|c1|integer|0||0
3|c2|integer|0||0
4|fid|integer|0||0
5|name|text|0||0
6|fid:1|integer|0||0
7|name:1|text|0||0

The problem is that I cannot seem to SELECT name:1. How can I do it other than going back and re-aliasing the columns?