SQL Join question

Peter Haworth pete at lcsql.com
Sun Jun 22 11:51:27 EDT 2014


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 <Mark_Smith at cpe.umanitoba.ca>
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
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



More information about the Use-livecode mailing list