mysql

An anonymous mysql question? Bitrot nails it there: T4 will just be a table showing the relationships of records/rows of data.

rokusan http://ask.metafilter.com/104872/mysql#1516120 "An anonymous mysql question?" It's election season; anonymous is cleaning up a voter file. (Or matching a volunteer file to a registered voter list, or maybe even ******** * **** ** ****** *****. If anonymous is helping ***** *** *** ***** ***** or *********** ** *******, anonymous should feel free to email me with questions! Deathalicious http://ask.metafilter.com/104872/mysql#1516124 "Since you want to keep the column order specific, it would help to be explicit when creating your insert:" Deathalicious's method will order the projection of columns in the select or insert. But it is very much not guaranteed to preserve row ordering. (An un-ordered select will generally be ordered by the best index the query optimizer finds, or by the an internal (temp file) sort and match algorithm.) If you want an explicit row ordering, you must say so, with an order by clause. And nothing, anonymous, is going to copy indices to your new table. You'll have to do that yourself, and without seeing the data, there's no way to advise you how best to do that, besides the obvious advice.

First, tables don't have any inherent order. You need to think about the relationships among rows in the various columns. But you probably want something like: INSERT INTO T4 SELECT T1.*, T2.*, T3.* FROM T3 JOIN T2 ON T3.foo = T2.foo JOIN T1 ON T3.foo = T1.foo

In theory tables don't have an order but in practice they might; I remember this particularly from Ruby on Rails where the order of fields appears to be based on the order of the corresponding columns in the tables. You can control this, at least to the extent of how the columns are listed in a "describe TABLENAME" command and probably in a "SELECT *" command as well. Since you want to keep the column order specific, it would help to be explicit when creating your insert: INSERT INTO T4 (firstcolumnoftable1, secondcolumnoftable1, thirdcolumnoftable1, fourthcolumnoftable1, ... , thirdcolumnoftable3, fourthcolumnoftable3) SELECT T1.firstcolumn, T1.secondcolumn, T1.thirdcolumn, ..., T3.thirdcolumn, T3.fourthcolumn FROM (T3 INNER JOIN T2 ON T3.key = T2.key) INNER JOIN T1 ON T2.key = T1.key SELECT * will probably work as well, but remember that if there is a shared key across these tables (and there has to be in order for this to make any sense) you probably only want it displayed once, not three times. Keep in mind that if you didn't use a unique identifier, the shared key may be a combination of fields (i.e firstname, lastname). You can still do a join this way: INSERT INTO CombinedInfo (firstName, lastName, age, gender, occupation, boss) SELECT PersonalInfo.firstName, PersonalInfo.lastName, PersonalInfo.age, PersonalInfo.gender, JobInfo.occupation, JobInfo.boss FROM PersonalInfo INNER JOIN JobInfo ON (PersonalInfo.lastName=JobInfo.lastName AND PersonalInfo.firstName=JobInfo.firstName) Unless there is a shared key acoss T1, T2, and T3 it is sort of meaningless to combine the tables.

Incidentally, depending on what you're using this for it may be more useful to have this as a view:CREATE VIEW CombinedInfo AS SELECT PersonalInfo.firstName, PersonalInfo.lastName, PersonalInfo.age, PersonalInfo.gender, JobInfo.occupation, JobInfo.boss FROM PersonalInfo INNER JOIN JobInfo ON (PersonalInfo.lastName=JobInfo.lastName AND PersonalInfo.firstName=JobInfo.firstName) For selects you can treat this exactly like a table. In some cases updates might even be possible as well. You won't be able to make inserts though, obviously.

rodgerd is right. Years ago I made the mistake of confusing the insert id with the chronological order of records in a table. Slowly my database corrupted.

Databases are both row and column agnostic. if you want a specific order for your rows OR your columns, you should specify this in your SQL. Although a particular table has a particular order for columns based on the original CREATE TABLE command, this is most definitely subject to change, and anybody that assumed a SELECT * would return columns in a particular order all the time is just asking for trouble (as rodgerd says). Getting back to the question, I think that, unless you intend to drop T1, T2 and T3, Deathalicious's suggestion of creating a view seems like the best solution, as it doesn't needlessly replicate data. Note that if the row order is important to you, you should specify an ORDER BY clause in the SELECT statement used to construct the view, otherwise you have all the problems orthogonality mentions... :)

Anonymous, it sounds like you might be trying to denormalize your database. You wouldn't do something that foolish, would you?

There are http://www.siue.edu/~dbock/cmis564/denormal.htm. Not saying that this is definitely one of those cases, but the cases do occur.