select _rowid_, * from tab3 does the trick - thanks all On 16 September 2014 13:13, Paul Sanderson <sandersonforens...@gmail.com> wrote:
> _rowid_ is probably the answer with a temporary table > > On 16 September 2014 13:00, Paul Sanderson <sandersonforens...@gmail.com> > wrote: > >> Thanks - I like the temporary table idea, but now sure how it would work. >> >> say for instance I have two tables >> >> create tab1 (id int, name text) >> 1, 'paul' >> 2, 'helen' >> 3, 'melanie' >> create tab2 (id int, country text) >> 1, 'uk' >> 2, 'scotland' >> >> I can create a temporary table >> create table tab3 as select * from tab1, tab2 >> >> and I get values >> >> 1|paul|1|uk >> 1|paul|2|scotland >> 2|helen|1|uk >> 2|helen|2|scotland >> 3|melanie|1|uk >> 3|melanie|2|scotland >> >> what I want is >> >> 1|1|paul|1|uk >> 2|1|paul|2|scotland >> 3|2|helen|1|uk >> 4|2|helen|2|scotland >> 5|3|melanie|1|uk >> 6|3|melanie|2|scotland >> >> How do I get that extra column? >> >> Cheers >> Paul >> >> >> >> >> >> On 16 September 2014 12:26, Dave Wellman <dwell...@ward-analytics.com> >> wrote: >> >>> Hi, >>> If you can insert into another table then you might want to use a >>> trigger >>> on the target table. I've done that with good effect (only on low volumes >>> though, I don't know what would happen on larger volume and if you have >>> larger volumes). >>> Cheers, >>> Dave >>> >>> >>> Ward Analytics Ltd - information in motion >>> Tel: +44 (0) 118 9740191 >>> Fax: +44 (0) 118 9740192 >>> www: http://www.ward-analytics.com >>> >>> Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, >>> United Kingdom, GU1 3SR >>> Registered company number: 3917021 Registered in England and Wales. >>> >>> >>> -----Original Message----- >>> From: sqlite-users-boun...@sqlite.org >>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J Decker >>> Sent: 16 September 2014 12:02 >>> To: General Discussion of SQLite Database >>> Subject: Re: [sqlite] Create join and add unique column >>> >>> could create a temporary table with a incrementing key and 'insert into >>> temp_table select .... join ...' something like that? >>> maybe use the existing keys and create a composite key sorta thing? >>> >>> On Tue, Sep 16, 2014 at 3:51 AM, Paul Sanderson < >>> sandersonforens...@gmail.com> wrote: >>> >>> > I want to create a join on two tables and add a unique number to each >>> > returned row. Can this be done with a SQL query? >>> > >>> > Thanks >>> > >>> > >>> > -- >>> > Paul >>> > www.sandersonforensics.com >>> > skype: r3scue193 >>> > twitter: @sandersonforens >>> > Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 >>> > 572786 >>> > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - >>> > Deleted SQLite recovery >>> > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC >>> > processing made easy _______________________________________________ >>> > sqlite-users mailing list >>> > sqlite-users@sqlite.org >>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> > >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> >> >> >> -- >> Paul >> www.sandersonforensics.com >> skype: r3scue193 >> twitter: @sandersonforens >> Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 >> 572786 >> http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - >> Deleted SQLite recovery >> http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC >> processing made easy >> >> > > > -- > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 > 572786 > http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - > Deleted SQLite recovery > http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC > processing made easy > > -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786+44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?190-SQLite-Recovery - Deleted SQLite recovery http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
