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