combing 3 tables into one?

vocoder

is there an easy way to do this? each table has about 20,000 records in it. each table has a common date, and a common sequence number, so for example

date, sequence, area, resolution

date, sequence, name, lat, long

date, sequence, amount, type

i want to create a new table that has

date, sequence, area, resolution, name, lat, long, amount, type

is this possible using a single query? I tried to do it using a single query and managed to lock up mysql, doh.

any help on how to do this would be greatly appreciated.

Pig

Are you looking to combine records, based off the date and/or sequence?

If you aren't, the method would be to create a new table with all the fields you need.

Then query each entire table and run through the array inserting each row.

$result = mysql_query("SELECT * FROM table");
while( $row = mysql_fetch_array($result) ) safe_query("INSERT INTO new_table date, sequence, area, resolution VALUES ('$row[date]', '$row['sequence'], '$row[area]', '$row[resolution]')");

Do that for each table, and you are done.

If you are looking to combine, you would have to query for each record being inserted and check to see if the key already exists, and update instead of insert.

I suppose you could put everything in an array and manipulate. That would avoid doing the extra query for every entry. That's a lot more work though.

HTH

vocoder

right, to explain it a bit more, lets say the tables are setup this way

date, sequence, area, resolution, extra1, extra2, extra3

date, sequence, name, lat, long, extra1, extra2, extra3

date, sequence, amount, type, extra1, extra2, extra3

the "extra" fields are actaully called something else, I just don't need that information in the new table.

so then I have a new table I have created with just the fields I want

date, sequence, area, resolution, name, lat, long, amount, type

Now, what I want to do is to query each of the 3 tables and then "join" the information together to create the new record in the new table, based on the date and sequence number of each old table. The problem seems to be that I have about 20,000 records in each table so it takes so much time that sometimes it appears to crash mysql, and other times it times out on me. That says to me that I am doing something wrong. The good thing is that this only has to be done once a night.

Pig

Well, you can get around the mysql limitations by merging everything in an array. Select everything from the table, and put it into a mulitdimensional array. Let's say that the combination of date, and sequence is unique. Then you could do something like:

$array[date][sequence] =
{
     [area]
     [resolution]
}

Then, when you get the second and thrid tables data, you can access that peice of the array because the date and sequence are the same, then add to your array and it would look like this:

$array[date][sequence] =
{
     [area]
     [resolution]
     [name]
     [lat]
     [long]
     [amount]
}

If the combiniation of date and sequence doesn't exist, then it will be created. If it does, then just add on array.

From there it is a simple matter of looping through $array and inserting it into your mysql database.

Of course, the structure of the array will really depend on the structure of the data in your tables, but you should be able to sort that out.

I don't know exactly what you are trying to ultimately accomplish, but it sounds like you have some process poorly or strangely structured if you have to do this. You should be able to get all of the info from a simple join, and that would 1,000's of times more effecient to do for a handful of queries than to run a process like this on entire tables.

ultraslacker

Originally posted by vocoder
The problem seems to be that I have about 20,000 records in each table so it takes so much time that sometimes it appears to crash mysql, and other times it times out on me. That says to me that I am doing something wrong.

20k rows are not a problem, but a mistake in a join could square or cube the results. What is the query you are attempting? Post the explain output also. (EXPLAIN SELECT * FROM etc)

Danicek

It should be possible to do it in one query.
However making it in three steps would be easier.

P. S.: Does mySQL support
Insert Into table_name Select...?

Drakla

Yup, MySQL does do the Insert/Select thing and also has Replace/Select. If it could do update and select together I'd love it even more.