SQL Join question

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

SQL Join question

Mark Smith
I have two tables A and B, both with the same structure (about 50 columns each). I would like to combine them into a single table (ie. rows from Table A followed by rows from Table B). Does anyone know of an SQL statement that will do that?

Thanks
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

John Craig-4
What database are you using?  The following should work with MySL (and
maybe others)

If tableA was empty or both tables didn't have any overlapping primary
key values, then this would work, but I'm assuming there's a primary key
in there that would mess things up.
INSERT INTO tableA SELECT * FROM tableB

If you have an auto incrementing primary key 'id' as the first field in
both tables...
INSERT INTO tableA SELECT 0, field1, field2, field3, etc...  FROM tableB

If you want to overwrite any conflicting records in tableA with the
records from tableB...
REPLACE INTO tableA SELECT * FROM tableB

HTH


On 21/06/2014 15:57, Mark Smith wrote:

> I have two tables A and B, both with the same structure (about 50 columns
> each). I would like to combine them into a single table (ie. rows from Table
> A followed by rows from Table B). Does anyone know of an SQL statement that
> will do that?
>
> Thanks
>
>
>
> --
> View this message in context: http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

John Craig-4
I really should have started with '0, field2' in the example below -
assuming field 1 is the primary key..
> If you have an auto incrementing primary key 'id' as the first field
> in both tables...
> INSERT INTO tableA SELECT 0, field1, field2, field3, etc...  FROM tableB
>


_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Peter Haworth
In reply to this post by Mark Smith
SELECT * FROM TableA UNION SELECT * FROM TableB

Both tables must have the same number of columns. You'll end up with
separate Records from each table so if you need to differentiate between
them and there isn't a column that does that, you could do something like

SELECT 'A',* FROM tableA UNION SELECT 'B',* FROM tableB

You'll probably need to sort the Records so the related entries appear
together assuming the two tables each have a column that relate them to
each other.

If there is a column that ties the tables together then

SELECT tableA.*,tableB.* FROM tableA JOIN tableB ON
tableB.keycolumn=tableA.keycolumn

Pete
lcSQL Software
On Jun 21, 2014 7:57 AM, "Mark Smith" <[hidden email]> wrote:

> I have two tables A and B, both with the same structure (about 50 columns
> each). I would like to combine them into a single table (ie. rows from
> Table
> A followed by rows from Table B). Does anyone know of an SQL statement that
> will do that?
>
> Thanks
>
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Peter Haworth
In reply to this post by Mark Smith
Forget my earlier post, thought you were wanting to select not insert.
John's INSERT...... SELECT is the way to do it.

Pete
lcSQL Software
On Jun 21, 2014 7:57 AM, "Mark Smith" <[hidden email]> wrote:

> I have two tables A and B, both with the same structure (about 50 columns
> each). I would like to combine them into a single table (ie. rows from
> Table
> A followed by rows from Table B). Does anyone know of an SQL statement that
> will do that?
>
> Thanks
>
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Peter Haworth
Just looking at this again, I don't think the primary key column is an
issue if the goal is to put all tableB rows into tableA.  You'll have to
enumerate all the tableA and tableB columns omitting the primary key column
for each one:

INSERT INTO tableA (colA1,colA2,colA3.....,colAn) SELECT
colB1,ColB2,ColB3......,ColBn FROM tableB

... where the primary key column isn't named for either table.  That way a
new primary key value will be created automatically for each new row.  All
that, of course, assuming there is an INTEGER PRIMARY KEY column defined in
tableA

As an aside, it isn't necessary to include AUTOINCREMENT on the primary key
column to have it automatically calculated, at least in SQLite, all that's
needed is to define the column as INTEGER PRIMARY KEY.

With no AUTOINCREMENT, a newly inserted row will usually get the next
highest primary key value but there are exceptions.  For example, if the
highest possible primary key value has been reached, then any value made
available by the deletion of rows will be used.

With AUTOINCREMENT, every new row is guaranteed to have a primary key value
1 higher than the previous highest value so if you reach the highest
possible primary key value and then insert a new row, you'll get an error.

There's really isn't a practical difference since I think the maximum
possible highest key value is 2 to the power of 64.

Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Sat, Jun 21, 2014 at 9:29 AM, Peter Haworth <[hidden email]> wrote:

> Forget my earlier post, thought you were wanting to select not insert.
> John's INSERT...... SELECT is the way to do it.
>
> Pete
> lcSQL Software
> On Jun 21, 2014 7:57 AM, "Mark Smith" <[hidden email]> wrote:
>
>> I have two tables A and B, both with the same structure (about 50 columns
>> each). I would like to combine them into a single table (ie. rows from
>> Table
>> A followed by rows from Table B). Does anyone know of an SQL statement
>> that
>> will do that?
>>
>> Thanks
>>
>>
>>
>> --
>> View this message in context:
>> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574.html
>> Sent from the Revolution - User mailing list archive at Nabble.com.
>>
>> _______________________________________________
>> use-livecode mailing list
>> [hidden email]
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>>
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Mark Smith
In reply to this post by John Craig-4
John Craig-4 wrote
I really should have started with '0, field2' in the example below -
assuming field 1 is the primary key..
> If you have an auto incrementing primary key 'id' as the first field
> in both tables...
> INSERT INTO tableA SELECT 0, field1, field2, field3, etc...  FROM tableB
>
Hi John, good guess on field1 being an auto incrementing primary key. If you don't mind me asking,what does the leading '0' in your insert statement select?

This discussion btw, reminds me that I have another problem. We started out in a pilot collecting data on one iPad when the volume increased to the point where a second unit was required. There are 3 tables in the database and they are all linked on the auto incrementing primary key in table 1 (ie. table 1 holds the primary key that links all 3 tables). With 2 iPads in the field I now have duplicate primary keys (1 set on each iPad). I never did resolve how to make the primary keys unique across devices and would welcome suggestions on how to do that. At present what I think I will do is just export these files into excel and add a column with a device name to distinguish the two sets and then combine then into 1 set of files (using excel, or cut and past… total volumes are actually quite low it just took time to enter the data which is why they ended up needing two iPads).  

Thanks for the advice

Mark
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Mark Smith
In reply to this post by Peter Haworth
Peter Haworth wrote
SELECT tableA.*,tableB.* FROM tableA JOIN tableB ON
tableB.keycolumn=tableA.keycolumn
Hi Peter. So, do I understand correctly that if table A has fields first, last and table B has fields age, sex and they both have an id column (key column) then this code would produce a file with fields id, first, last, age, sex?

And also, that I could do this without having to name each of the columns individually? If so, this is great because I need to do that as well (and there are hundreds of columns). My simple database design has 3 tables (a, b, c) which have unique sets of columns except for a matching ID field. It would be really great if I could combine all 3 tables into 1 for our data analysis phase (i.e. width wise, so that if there are 100 records in each table with 50 columns each the output has 100 records with 150 columns).

And then, as per my response to John, combine all of the records from the first device with all of the records from the second device so that I have just one great big rectangular table to analyze (ie. so if there are 100 records on the first device and 100 on the second the result (after JOINing the tables on each device) would be 200 records by 150 columns). So I think the plan would be to run your code above to create 1 rectangular table consisting of all columns and rows on each device, then export to excel and combine them into 1 file in excel (with an added field indicating which device the records came from).

Does this makes sense?

Mark
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Peter Haworth
Hi Mark,
Yes, you would end up with one row containing all the columns from all
three tables, but if there are three tables, the SELECT becomes:

SELECT tableA.*, tableB.*, tableC.* FROM tableA JOIN tableB ON
tableB.<keycolumn>=tableA.<keycolumn> JOIN tableC ON
tableC.<keycolumn>=tableA.<keycolumn>

But the keycolumn must have the same value for all three tables.


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Sun, Jun 22, 2014 at 7:23 AM, Mark Smith <[hidden email]>
wrote:

> Peter Haworth wrote
> > SELECT tableA.*,tableB.* FROM tableA JOIN tableB ON
> > tableB.keycolumn=tableA.keycolumn
>
> Hi Peter. So, do I understand correctly that if table A has fields first,
> last and table B has fields age, sex and they both have an id column (key
> column) then this code would produce a file with fields id, first, last,
> age, sex?
>
> And also, that I could do this without having to name each of the columns
> individually? If so, this is great because I need to do that as well (and
> there are hundreds of columns). My simple database design has 3 tables (a,
> b, c) which have unique sets of columns except for a matching ID field. It
> would be really great if I could combine all 3 tables into 1 for our data
> analysis phase (i.e. width wise, so that if there are 100 records in each
> table with 50 columns each the output has 100 records with 150 columns).
>
> And then, as per my response to John, combine all of the records from the
> first device with all of the records from the second device so that I have
> just one great big rectangular table to analyze (ie. so if there are 100
> records on the first device and 100 on the second the result (after JOINing
> the tables on each device) would be 200 records by 150 columns). So I think
> the plan would be to run your code above to create 1 rectangular table
> consisting of all columns and rows on each device, then export to excel and
> combine them into 1 file in excel (with an added field indicating which
> device the records came from).
>
> Does this makes sense?
>
> Mark
>
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574p4680591.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Peter Haworth
In reply to this post by Mark Smith
On Sun, Jun 22, 2014 at 6:55 AM, Mark Smith <[hidden email]>
wrote:

> I never did
> resolve how to make the primary keys unique across devices
>

Assuming you are using SQLite, there are a couple of ways to achieve this,
one riskier than the other.

The less risky approach is to give up on using an autoincrementing primary
key field and use your own counter along with a device identifier column as
a prefix.  The two columns together would become your primary key column.
 The CREATE TABLE statement for this would something like:

CREATE TABLE mytable (Device TEXT DEFAULT 'D1', Recno INTEGER,...other
columns) PRIMARY KEY (Device,Recno)

The Device column would have a different default value on each iPad, or you
could omit the DEFAULT specification and your application could supply the
appropriate value on each iPad.

Your application would be responsible for incrementing the Recno column for
each row added to the table.  Alternatively, you could simply set it to the
milliseconds when each row is added.

The result would be that each row would have a unique primary key
consisting of the Device and Recno concatenated together and since the
Device column would be different on each device, you're guaranteed that
there would be no duplicates across devices.

The riskier one involves using an internal SQLite table named
sqlite_sequence.  This table has 2 columns, table and seq.  There is one
row for each table in the db with the seq column containing the last used
primary key value.

When you first install your db on each iPad, you could set the seq field
for the table to a value that would guarantee there would never be an
overlap.  For example, if you know that there will never be more than
499,999 rows in the tabnle on any individual iPad, iPad 1 would have seq
zero, iPad 2 would have seq 499,999, ipad 3 would have seq 999,999.  You
can do that with a simple INSERT statement on the sqlite_sequence table but
make sure the database is empty when you do it.


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Mark Smith
Hi Peter, thanks for that. I definitely prefer your option 1. Would it still be possible to have an auto incrementing field that combines with the device id to make the primary key (ie. could recno in your example be auto incrementing)? Also, I wonder if there is a device ID that could be grabbed from the device itself? Combining the two would make this foolproof since no manual intervention would be required, and each iPad device would be guaranteed to have/contribute its own unique id.

Peter Haworth wrote
Assuming you are using SQLite, there are a couple of ways to achieve this,
one riskier than the other.

The less risky approach is to give up on using an autoincrementing primary
key field and use your own counter along with a device identifier column as
a prefix.  The two columns together would become your primary key column.
 The CREATE TABLE statement for this would something like:

CREATE TABLE mytable (Device TEXT DEFAULT 'D1', Recno INTEGER,...other
columns) PRIMARY KEY (Device,Recno)
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Mark Smith
In reply to this post by Peter Haworth
Perfect, thank you.

Peter Haworth wrote
but if there are three tables, the SELECT becomes:

SELECT tableA.*, tableB.*, tableC.* FROM tableA JOIN tableB ON
tableB.<keycolumn>=tableA.<keycolumn> JOIN tableC ON
tableC.<keycolumn>=tableA.<keycolumn>

But the keycolumn must have the same value for all three tables.
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Peter Haworth
In reply to this post by Mark Smith
Unfortunately, autoincrementing can only be done on primary keys and there
can only be one primary key per table.

However, it just occurred to me that if you go back to an autoincrementing
primary key you could create the concatenated key in your SELECT with:

SELECT Device || pKey AS UniqueKey,.....

"||" is the SQLite concatenation operator so the SELECT would create a
column named UniqueKey.

Don't know enough about iOS to know if there is some sort of unique ID you
could get from it to use as the device column but that would work great if
so.


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>


On Sun, Jun 22, 2014 at 3:15 PM, Mark Smith <[hidden email]>
wrote:

> Hi Peter, thanks for that. I definitely prefer your option 1. Would it
> still
> be possible to have an auto incrementing field that combines with the
> device
> id to make the primary key (ie. could recno in your example be auto
> incrementing)? Also, I wonder if there is a device ID that could be grabbed
> from the device itself? Combining the two would make this foolproof since
> no
> manual intervention would be required, and each iPad device would be
> guaranteed to have/contribute its own unique id.
>
>
> Peter Haworth wrote
> > Assuming you are using SQLite, there are a couple of ways to achieve
> this,
> > one riskier than the other.
> >
> > The less risky approach is to give up on using an autoincrementing
> primary
> > key field and use your own counter along with a device identifier column
> > as
> > a prefix.  The two columns together would become your primary key column.
> >  The CREATE TABLE statement for this would something like:
> >
> > CREATE TABLE mytable (Device TEXT DEFAULT 'D1', Recno INTEGER,...other
> > columns) PRIMARY KEY (Device,Recno)
>
>
>
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574p4680596.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Glen Bojsza
In reply to this post by Mark Smith
Austin!
Time for BBQ.
How long are you there?

thanks,

Glen

> On Jun 21, 2014, at 10:57 AM, Mark Smith <[hidden email]> wrote:
>
> I have two tables A and B, both with the same structure (about 50 columns
> each). I would like to combine them into a single table (ie. rows from Table
> A followed by rows from Table B). Does anyone know of an SQL statement that
> will do that?
>
> Thanks
>
>
>
> --
> View this message in context: http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Mark Smith
In reply to this post by Peter Haworth
Thanks Peter. Good discussion. I'll check on the developer list to see if anyone knows if there is an iPad device ID I can grab. Perhaps another widget for Monte to work on :)
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

Kay C Lan
On Mon, Jun 23, 2014 at 11:14 AM, Mark Smith
<[hidden email]> wrote:
> Thanks Peter. Good discussion. I'll check on the developer list to see if
> anyone knows if there is an iPad device ID I can grab. Perhaps another
> widget for Monte to work on :)
>
I know I'm late to the thread (just enjoyed 2 weeks bicycling from
Amsterdam to Frankfurt with my wife:-) but I thought I might point out
this clause of the iOS Developer Program License Agreement:

3.3.9     You and Your Applications…. Further, neither You nor Your
Application will use any permanent, device-based identifier, or any
data derived therefrom, for purposes of uniquely identifying a device.

This appears to be the result of this:

http://blogs.avg.com/mobile/apple-ios-7-puts-unique-device-ids/

So it looks like UDIDs and MAC Addresses are off limits but the
'Advertising IDs' are the way to go.

Sorry, I have no idea how to create or crab one of those.

_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Reply | Threaded
Open this post in threaded view
|

Re: SQL Join question

divya navaneeth
In reply to this post by Mark Smith
Hi Mark Smith,
     You just try this query "select  A.col1,A.col2 etc ,B.col1,B.col2 etc
from table A inner join table B on A.id=B.id"


On Sat, Jun 21, 2014 at 8:27 PM, Mark Smith <[hidden email]>
wrote:

> I have two tables A and B, both with the same structure (about 50 columns
> each). I would like to combine them into a single table (ie. rows from
> Table
> A followed by rows from Table B). Does anyone know of an SQL statement that
> will do that?
>
> Thanks
>
>
>
> --
> View this message in context:
> http://runtime-revolution.278305.n4.nabble.com/SQL-Join-question-tp4680574.html
> Sent from the Revolution - User mailing list archive at Nabble.com.
>
> _______________________________________________
> use-livecode mailing list
> [hidden email]
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



--

   Thank You
Divya S Sathyan
_______________________________________________
use-livecode mailing list
[hidden email]
Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode