Create join and add unique column

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

Create join and add unique column

Paul Sanderson
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <
[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

John McKown
In reply to this post by Paul Sanderson
On Tue, Sep 16, 2014 at 5:51 AM, Paul Sanderson
<[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

Dave Wellman
In reply to this post by J Decker
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: [hidden email]
[mailto:[hidden email]] 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 <
[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
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: [hidden email]
> [mailto:[hidden email]] 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 <
> [hidden email]> 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
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
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 <[hidden email]>
> 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: [hidden email]
>> [mailto:[hidden email]] 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 <
>> [hidden email]> 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
>> > [hidden email]
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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" <[hidden email]> ha
scritto:

> _rowid_ is probably the answer with a temporary table
>
> On 16 September 2014 13:00, Paul Sanderson <[hidden email]>
> 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 <[hidden email]>
> > 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: [hidden email]
> >> [mailto:[hidden email]] 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 <
> >> [hidden email]> 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
> >> > [hidden email]
> >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> >
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

Paul Sanderson
In reply to this post by Paul Sanderson
select _rowid_, * from tab3 does the trick - thanks all

On 16 September 2014 13:13, Paul Sanderson <[hidden email]>
wrote:

> _rowid_ is probably the answer with a temporary table
>
> On 16 September 2014 13:00, Paul Sanderson <[hidden email]>
> 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 <[hidden email]>
>> 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: [hidden email]
>>> [mailto:[hidden email]] 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 <
>>> [hidden email]> 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
>>> > [hidden email]
>>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>> >
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

R Smith

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
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 <[hidden email]> 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
>> [hidden email]
>> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

James K. Lowden
In reply to this post by R Smith
On Tue, 16 Sep 2014 16:38:09 +0200
RSmith <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

R Smith

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

jose isaias cabrera
In reply to this post by R Smith

"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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
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
> [hidden email]
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

Petite Abeille-2
In reply to this post by R Smith

On Sep 16, 2014, at 8:15 PM, RSmith <[hidden email]> 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
  from        NameSet
  cross join  CountrySet
)
select    count( * ) 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
on        self.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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Create join and add unique column

Keith Medcalf
In reply to this post by R Smith

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: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of RSmith
>Sent: Tuesday, 16 September, 2014 08:38
>To: [hidden email]
>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
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users