DF-21 Forums

[Lionel Fouillen]

Hi,

I have a question here about something I never had to do in SQL development. Maybe one of you will be able to help me.

I have 3 tables with identical number of rows (number of fields does not matter, all fields are distinct across the 3 tables).
I want to select all data from those tables and unify them by inserting them intoa 4th table. So I would have all my data fields in a single table instead of having them spread across 3 tables. Do you get the idea?

So to make it clear, I have:
Table1 containing fields a,b
Table2 containing fields c,d,e
Table3 containing field f

I want to put all data from these into a new Table4 which will contain a,b,c,d,e,f.
Table4 was already created and is awaiting for data.

How to do that? Which SQL instruction(s) should I use?

Thanks.

[The MAZZTer]

FYI combining tables only works well if one row in each table corresponds to 0 or 1 rows in the others. Otherwise you have redundant data which is difficult to maintain (and you're better off having multiple tables).

You seem to do this so you're ok.

However one thing you don't seem to have is a way to link one row in a table to another row in another table... this is a basic SQL operation (called a foreign key) and I'm not sure if you can programmatically join the tables without them.

If they all had an ID field you could do it like this:

INSERT INTO Table4 (a, b, c, d, e, f) SELECT Table1.a, Table1.b, Table2.c, Table2.d, Table2.e, Table3.f FROM Table1, Table2, Table3 WHERE Table1.id == Table2.id AND Table1.id == Table3.id

But since they don't MySQL has no way to know how the records are related. You'll have to write code to "SELECT * FROM Table1" and the same for 2 and 3 and iterate over all the records, read them, combine them, and insert them into Table4 one by one.

_________________
http://www.mzzt.net/ | I am a respectable admin with a respectable sig.

[Lionel Fouillen]

Thanks! So I'll just create one more field in each table with auto-increment integer to have a ground for comparison.

[The MAZZTer]

That could work... >_> Except you may have to assign them initial values by hand.

_________________
http://www.mzzt.net/ | I am a respectable admin with a respectable sig.

[Lionel Fouillen]

No, it worked. I used phpMyAdmin to create an additional field with auto-increment and it filled all rows automatically.

I did it in the 3 tables, then used the SQL sequence you mentioned above and all was done within seconds!. Except that I used a simple "=". The quivalence "==" was refused by phpMyAdmin.

[The MAZZTer]

Yeah it would be =, I'm used to C++ and PHP etc

_________________
http://www.mzzt.net/ | I am a respectable admin with a respectable sig.