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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users