mysql
October 21, 2008 9:15 PM Subscribe
A MySQL question.
I have a database with some tables T1 T2 T3.
Now T1 and T2 have some names in one column and some indices and other numbers associated with these names in other columns.
T3 has a bunch of columns: the first two of these are the indices and other numbers of T1 and T2, and T3 has other data of its own in other columns associated with these first two columns.
Now I need to make a new table, that builds its columns by simply copy pasting columns from T1 T2 T3 while maintaining the corresponding order. Call this table T4. Think of T4's columns as a mapping from T3 to T2 and T1 such that all the indices and table specific numbers are now in one table and can be read off.
How do I write this? Can you please help me with the MySQL code?
Please don't ask me to upload all the data again, this is not possible.
I have a database with some tables T1 T2 T3.
Now T1 and T2 have some names in one column and some indices and other numbers associated with these names in other columns.
T3 has a bunch of columns: the first two of these are the indices and other numbers of T1 and T2, and T3 has other data of its own in other columns associated with these first two columns.
Now I need to make a new table, that builds its columns by simply copy pasting columns from T1 T2 T3 while maintaining the corresponding order. Call this table T4. Think of T4's columns as a mapping from T3 to T2 and T1 such that all the indices and table specific numbers are now in one table and can be read off.
How do I write this? Can you please help me with the MySQL code?
Please don't ask me to upload all the data again, this is not possible.
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:
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.
posted by Deathalicious at 9:45 PM on October 21, 2008
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.
posted by Deathalicious at 9:45 PM on October 21, 2008
Incidentally, depending on what you're using this for it may be more useful to have this as a view:
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.
posted by Deathalicious at 9:47 PM on October 21, 2008
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.
posted by Deathalicious at 9:47 PM on October 21, 2008
rokusan writes "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 writes "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.
posted by orthogonality at 10:17 PM on October 21, 2008 [1 favorite]
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 writes "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.
posted by orthogonality at 10:17 PM on October 21, 2008 [1 favorite]
In theory tables don't have an order but in practice they might
Sure they do. They have an order based on various DB internals. Relying on that order is something I wish I could summarily execute programmers for, because it's an ordering which can change on dump/restore for disaster recover, or server migrations, or from dev to test to production environments.
Never rely on it.
posted by rodgerd at 12:11 AM on October 22, 2008 [4 favorites]
Sure they do. They have an order based on various DB internals. Relying on that order is something I wish I could summarily execute programmers for, because it's an ordering which can change on dump/restore for disaster recover, or server migrations, or from dev to test to production environments.
Never rely on it.
posted by rodgerd at 12:11 AM on October 22, 2008 [4 favorites]
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.
posted by MaxK at 12:19 AM on October 22, 2008
posted by MaxK at 12:19 AM on October 22, 2008
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... :)
posted by ranglin at 1:00 AM on October 22, 2008
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... :)
posted by ranglin at 1:00 AM on October 22, 2008
Anonymous, it sounds like you might be trying to denormalize your database. You wouldn't do something that foolish, would you?
posted by I_pity_the_fool at 1:52 AM on October 22, 2008
posted by I_pity_the_fool at 1:52 AM on October 22, 2008
There are valid reasons to denormalize databases. Not saying that this is definitely one of those cases, but the cases do occur.
posted by mmascolino at 6:09 AM on October 22, 2008
posted by mmascolino at 6:09 AM on October 22, 2008
Yeah, I can't tell you how many times a delightfuly relational database could have improved by having several tables smooshed together. In theory, having repeated data is bad. In practice, it's often easier, faster, and better. I remember reading somewhere that a good database shouldn't have fields that are often null; these should be normalized into another table. Really? Now that's just getting crazy.
posted by Deathalicious at 12:18 AM on October 23, 2008
posted by Deathalicious at 12:18 AM on October 23, 2008
« Older My fever didn't reach 103, so I'm not hot-blooded. | How to get an InfoMania background music to work... Newer »
This thread is closed to new comments.
Bitrot nails it there: T4 will just be a table showing the relationships of records/rows of data.
posted by rokusan at 9:40 PM on October 21, 2008 [1 favorite]