Re: [sqlite] Create join and add unique column

[Keith Medcalf]

You will also note that doing something like:

create [temporary] table x as select a, b, ... from y ...

will result in calculated columns being inserted with affinity None.  If you 
need a specific affinity you need to cast the expression result to that type 
affinity.  Also, if it matters, text columns will not carry over the collation 
to the new table definition.  If you need the columns to have a specific 
collation, or columns containing computed results to have a specific affinity 
(and you do not want to always have to cast(...) either the source or 
destination, then you need to create the destination table first, then insert 
into ... as select ...;


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of RSmith
>Sent: Tuesday, 16 September, 2014 08:38
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Create join and add unique column
>
>
>On 2014/09/16 15:32, Paul Sanderson wrote:
>> select _rowid_, * from tab3 does the trick - thanks all
>
>Indeed, and if you are pedantic or do not work in a table with rowids,
>the solution is to explicitly give the table definition then
>fill it, some variation on this:
>
>CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT,
>name TEXT, country TEXT);
>INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
>tab2 C;
>
>tab 3 should now look like this (according to your sample tables):
>rowNo|   name   |   country
>-
> 1 |  paul   | uk
> 2 |  paul   | scotland
> 3 |  helen | uk
> 4 |  helen | scotland
> 5 |  melanie | uk
> 6 |  melanie | scotland
>
>
>NOTE:
>In-case you are not familiar with it - That insert omits the rowNo and
>can be thought of as a variation of this query which achieves
>the same:
>INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
>FROM tab1 N, tab2 C;
>
>
>Hope that widens your SQL arsenal another micron, Cheers!
>Ryan
>
>
>___
>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

Re: [sqlite] Create join and add unique column

[Petite Abeille]

On Sep 16, 2014, at 8:15 PM, RSmith  wrote:

> could you show how to achieve this in SQL via the ranking method you linked

Well, ranking is the same as numbering, no?

So, for example:

with
NameSet
as
(
  select  1 as id, 'paul' as name union all
  select  2 as id, 'helen' as name union all
  select  3 as id, 'melanie' as name
),
CountrySet
as
(
  select  1 as id, 'uk' as name union all
  select  20 as id, 'scotland' as name -- 
),
DataSet
as
(
  select  NameSet.id || '.' || CountrySet.id as key,
  NameSet.id as name_id,
  NameSet.name as name_name,
  CountrySet.id as country_id,
  CountrySet.name as country_name
  fromNameSet
  cross join  CountrySet
)
selectcount( * ) as id,
  DataSet.name_id as name_id,
  DataSet.name_name as name_name,
  DataSet.country_id as country_id,
  DataSet.country_name as country_name
from  DataSet

join  DataSet self
onself.key >= DataSet.key

group by  DataSet.name_id,
  DataSet.name_name,
  DataSet.country_id,
  DataSet.country_name

order by  1;


> id|name_id|name_name|country_id|country_name
> 1|3|melanie|2|scotland
> 2|3|melanie|1|uk
> 3|2|helen|2|scotland
> 4|2|helen|1|uk
> 5|1|paul|2|scotland
> 6|1|paul|1|uk


Or something :D

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

It all helped me thanks - just not in solving this particular problem
(which I have now manage to do)

Thanks all.

On 16 September 2014 19:33, jose isaias cabrera 
wrote:

>
> "RSmith" wrote...
>
>
>> On 2014/09/16 15:32, Paul Sanderson wrote:
>>
>>> select _rowid_, * from tab3 does the trick - thanks all
>>>
>>
>> Indeed, and if you are pedantic or do not work in a table with rowids,
>> the solution is to explicitly give the table definition then fill it, some
>> variation on this:
>>
>> CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT, country TEXT);
>> INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
>> tab2 C;
>>
>> tab 3 should now look like this (according to your sample tables):
>> rowNo|   name   |   country
>> -
>> 1 |  paul   | uk
>> 2 |  paul   | scotland
>> 3 |  helen | uk
>> 4 |  helen | scotland
>> 5 |  melanie | uk
>> 6 |  melanie | scotland
>>
>>
>> NOTE:
>> In-case you are not familiar with it - That insert omits the rowNo and
>> can be thought of as a variation of this query which achieves the same:
>> INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
>> FROM tab1 N, tab2 C;
>>
>>
>> Hope that widens your SQL arsenal another micron, Cheers!
>>
>
> Ryan,
>
> I don't know if it helped Paul at all, but it did widen my SQL arsenal a
> good 7-8 microns.  Thanks.
>
> josé
> ___
> 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

Re: [sqlite] Create join and add unique column

[jose isaias cabrera]

"RSmith" wrote...



On 2014/09/16 15:32, Paul Sanderson wrote:

select _rowid_, * from tab3 does the trick - thanks all


Indeed, and if you are pedantic or do not work in a table with rowids, the 
solution is to explicitly give the table definition then fill it, some 
variation on this:


CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name 
TEXT, country TEXT);
INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, 
tab2 C;


tab 3 should now look like this (according to your sample tables):
rowNo|   name   |   country
-
1 |  paul   | uk
2 |  paul   | scotland
3 |  helen | uk
4 |  helen | scotland
5 |  melanie | uk
6 |  melanie | scotland


NOTE:
In-case you are not familiar with it - That insert omits the rowNo and can 
be thought of as a variation of this query which achieves the same:
INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country 
FROM tab1 N, tab2 C;



Hope that widens your SQL arsenal another micron, Cheers!


Ryan,

I don't know if it helped Paul at all, but it did widen my SQL arsenal a 
good 7-8 microns.  Thanks.


josé 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[RSmith]

On 2014/09/16 20:00, James K. Lowden wrote:


Or not use a temporary table.

http://www.schemamania.org/sql/#rank.rows

Now easier with CTE.  In theory it could be *faster* than a temporary
table, because the insertion I/O is avoided.  But only testing will
tell.

--jkl


Hi James,

I'm well familiar with the ranking SQL (thanks to your site btw), but I fail to see how that can be applied to solve the OP's 
problem which I will list again hereunder.  I know the OP in the meantime realized he had other problems which renders any of these 
solutions unhelpful, but allow me the learning experience please, could you show how to achieve this in SQL via the ranking method 
you linked (or any other method you might come up with - I can do it in CTE, but am hoping to learn how to do without):


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'


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
 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[James K. Lowden]

On Tue, 16 Sep 2014 16:38:09 +0200
RSmith  wrote:

> On 2014/09/16 15:32, Paul Sanderson wrote:
> > select _rowid_, * from tab3 does the trick - thanks all
> 
> Indeed, and if you are pedantic or do not work in a table with
> rowids, the solution is to explicitly give the table definition

Or not use a temporary table.  

http://www.schemamania.org/sql/#rank.rows

Now easier with CTE.  In theory it could be *faster* than a temporary
table, because the insertion I/O is avoided.  But only testing will
tell.  

--jkl
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

Back to the drawing board.

Create table as creates the columns with types based on their declared
affinity - I particularly need to identify columns in the new table that
existed as blobs in the original tables, create table as gives these an
affinity of "", I really need the original type definition :(

On 16 September 2014 18:18, Paul Sanderson 
wrote:

> Thanks Ryan. That doesn't work for me though as I am looking for a generic
> solution that will work on multiple tables - so no hard coding of column
> definitions :(
>
> I think I am getting there
>
> On 16 September 2014 15:38, RSmith  wrote:
>
>>
>> On 2014/09/16 15:32, Paul Sanderson wrote:
>>
>>> select _rowid_, * from tab3 does the trick - thanks all
>>>
>>
>> Indeed, and if you are pedantic or do not work in a table with rowids,
>> the solution is to explicitly give the table definition then fill it, some
>> variation on this:
>>
>> CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT,
>> name TEXT, country TEXT);
>> INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
>> tab2 C;
>>
>> tab 3 should now look like this (according to your sample tables):
>> rowNo|   name   |   country
>> -
>> 1 |  paul   | uk
>> 2 |  paul   | scotland
>> 3 |  helen | uk
>> 4 |  helen | scotland
>> 5 |  melanie | uk
>> 6 |  melanie | scotland
>>
>>
>> NOTE:
>> In-case you are not familiar with it - That insert omits the rowNo and
>> can be thought of as a variation of this query which achieves the same:
>> INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
>> FROM tab1 N, tab2 C;
>>
>>
>> Hope that widens your SQL arsenal another micron, Cheers!
>> Ryan
>>
>>
>>
>> ___
>> 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

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

Thanks Ryan. That doesn't work for me though as I am looking for a generic
solution that will work on multiple tables - so no hard coding of column
definitions :(

I think I am getting there

On 16 September 2014 15:38, RSmith  wrote:

>
> On 2014/09/16 15:32, Paul Sanderson wrote:
>
>> select _rowid_, * from tab3 does the trick - thanks all
>>
>
> Indeed, and if you are pedantic or do not work in a table with rowids, the
> solution is to explicitly give the table definition then fill it, some
> variation on this:
>
> CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name
> TEXT, country TEXT);
> INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N,
> tab2 C;
>
> tab 3 should now look like this (according to your sample tables):
> rowNo|   name   |   country
> -
> 1 |  paul   | uk
> 2 |  paul   | scotland
> 3 |  helen | uk
> 4 |  helen | scotland
> 5 |  melanie | uk
> 6 |  melanie | scotland
>
>
> NOTE:
> In-case you are not familiar with it - That insert omits the rowNo and can
> be thought of as a variation of this query which achieves the same:
> INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country
> FROM tab1 N, tab2 C;
>
>
> Hope that widens your SQL arsenal another micron, Cheers!
> Ryan
>
>
>
> ___
> 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

Re: [sqlite] Create join and add unique column

[RSmith]

On 2014/09/16 15:32, Paul Sanderson wrote:

select _rowid_, * from tab3 does the trick - thanks all


Indeed, and if you are pedantic or do not work in a table with rowids, the solution is to explicitly give the table definition then 
fill it, some variation on this:


CREATE TEMPORARY TABLE tab3 (rowNo INTEGER PRIMARY KEY AUTOINCREMENT, name 
TEXT, country TEXT);
INSERT INTO tab3 (name, country) SELECT N.name, C.country FROM tab1 N, tab2 C;

tab 3 should now look like this (according to your sample tables):
rowNo|   name   |   country
-
1 |  paul   | uk
2 |  paul   | scotland
3 |  helen | uk
4 |  helen | scotland
5 |  melanie | uk
6 |  melanie | scotland


NOTE:
In-case you are not familiar with it - That insert omits the rowNo and can be thought of as a variation of this query which achieves 
the same:

INSERT INTO tab3 (rowNo, name, country) SELECT NULL, N.name, C.country FROM 
tab1 N, tab2 C;


Hope that widens your SQL arsenal another micron, Cheers!
Ryan


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

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

Re: [sqlite] Create join and add unique column

[Alessandro Marzocchi]

Try having a look at post "How to determine player's leaderboard rank
efficiently?" (24th August) this could give you idea on how to solve this
problem through temporary tables. Regards
Il 16/set/2014 14:14 "Paul Sanderson" <sandersonforens...@gmail.com> ha
scritto:

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

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

_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

Re: [sqlite] Create join and add unique column

[Paul Sanderson]

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

Re: [sqlite] Create join and add unique column

[Dave Wellman]

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

Re: [sqlite] Create join and add unique column

[John McKown]

On Tue, Sep 16, 2014 at 5:51 AM, Paul Sanderson
 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

Just a bit of thinking out loud, but I wonder if a RECURSIVE CTE could
be used to generate the number, somehow.

WITH RECURSIVE counter(x) AS )
   SELECT 1 AS x
   UNION ALL
   SELECT x+1 FROM counter)
SELECT x AS uniqueNo FROM counter
OUTER LEFT JOIN
( SELECT a.col1 AS col1 , b.col2 as col2 FROM table1 AS a
  JOIN
  table2 AS b
  ON a.col3 = b.col3) AS joinTable
ORDER BY col1;

That may well not work. I don't have anything around to try it on.
And, at 06:23 local time, with insufficient caffeine intake, that is
the best that I can do so far.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Create join and add unique column

[J Decker]

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