[rumbitas]

Hi, I'm trying to create a new table with 3 rows, 2 from 1 table and 1 from another.

The rows have no fields to make a join.

______TABLE1_____   __TABLE2__
starttime|endtime      co2
_________|_______   __________
12345678  1234578     7.4568
23456894  ...         7.4568
...                     ...

With the following query, in my core i5, appears to hang my pc.

CREATE TABLE `importco2` AS SELECT kw.starttime, kw.endtime, co2.co2 FROM importkw_model1 kw, data_co2 co2;

There is another way to paste this rows in a new table?

Thanks for your help.

This post has been edited by Atli: 01 September 2012 - 09:11 AM
Reason for edit:: Added [code] tags.

[Atli]

rumbitas, on 01 September 2012 - 02:51 PM, said:

The rows have no fields to make a join.

Then how are you planing to join the tables? If there is no specific way of joining the tables, then the database will use a Cross Join, which joins every row in TableA with every row in TableB (See the link for examples if this isn't clear.). If there is any decent amount of data in the two tables, this type of join has the potential to be very very slow.

rumbitas, on 01 September 2012 - 02:51 PM, said:

Hi, I'm trying to create a new table with 3 rows, 2 from 1 table and 1 from another.

Do you really need a physical table? In many cases a View is more practical.

[rumbitas]

Thanks. But this is the problem, I don't have any column in the table to make a join with a relationship.

[Atli]

Isn't there a Primary Key in those tables? There should always be a primary key.

[e_i_pi]

As Atli said, JOINing two tables without a clause will cause a CROSS JOIN to occur, which you may or may not know alternately as a cross-product (mathematical term). So, if you have these tables:
Colours

Red
Blue
Yellow

Vehicles

Car
Boat
Train

...and do a CROSS JOIN, you'll end up with:
CROSS JOIN

Red Car
Blue Car
Yellow Car
Red Boat
Blue Boat
Yellow Boat
Red Train
Blue Train
Yellow Train

So, if you are CROSS JOINing a table with 1000 rows against another table with 1000 rows, you'll end up with a table that has 1000000 rows. This can get very expensive very quickly.

Are you sure you need a table with this data? I have a feeling that you may be approaching the problem in the wrong manner. What are you trying to eventually achieve? i.e. - what will this table be used for?

This post has been edited by e_i_pi: 01 September 2012 - 08:38 PM

[rumbitas]

Yes, there isn't a primary key. It is a data obtained in google from different sources, this is the main reason because I don't have a primary key to join those tables.

After all, I have loaded them in a Excel Sheet and I have made a simple copy and paste. I have saved the result in a CSV file, and I have uploaded it to my database. That's all.

Thanks for all.

[Atli]

Well, regardless of where the data comes from, or how you import it into MySQL, you have to design the MySQL tables properly or you should expect problems like these. MySQL is not a spreadsheet application like Excel, and it's not a simple file-based database like CSV. It's a relational database, and it must be used as such.

Every table in a relational database must define a column (or columns) that allows you to uniquely identify each row. That is what we define as the primary key, and that is what you use when joining tables (usually).

Unlike with Spreadsheet applications and CSV files, you can not rely on databases like MySQL to store the rows in the same order you add them. How the rows are stored internally can be different, for various reasons, so it's quite possible that the database will return the rows ordered unexpectedly if you don't specify a way to properly order them.

Typically, in MySQL, you add a Primary Key that is an integer with the AUTO_INCREMENT flag set. That effectively creates a PK who's value is the row count. You can then insert data normally and use the PK (the row number) as a way to join tables.

An easy way to "emulate" the way you would join two spreadsheet tables in programs like Excel, based on row location, is to just define an auto increment PK on both tables and join them on that:

CREATE TABLE `numbers` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `number` INTEGER NOT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `words` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `word` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
);

INSERT INTO `numbers`(`number`)
VALUES (0), (1), (1), (2), (3), (5), (8);

INSERT INTO `words`(`word`)
VALUE ('Zero'), ('One'), ('One'), ('Two'), ('Three'), ('Five'), ('Eight');

There you have two tables, both with integers as PKS that increment automatically when rows are inserted. If the inserts are synchronized, like in my example, then you can simply join ON the PKs and list the number with the word side by side:

mysql> SELECT
    ->     `numbers`.`number`,
    ->     `words`.`word`
    -> FROM numbers
    -> JOIN words
    ->     ON numbers.`id` = words.`id`;
+--------+-------+
| number | word  |
+--------+-------+
|      0 | Zero  |
|      1 | One   |
|      1 | One   |
|      2 | Two   |
|      3 | Three |
|      5 | Five  |
|      8 | Eight |
+--------+-------+
7 rows in set (0.00 sec)

Of course, there is a major assumption there that the rows will always remain synchronized. In practice that may prove hard to maintain. Rows could be deleted from either table, leaving orphaned entries. And, worse, entries could be added to only one of the tables, leaving the auto incremented values jagged and out of sync.

This also cheats the "relational" part of the database system. Even though you are joining the tables, you have no relationship specified. You could create a 1:1 (one-to-one) relationship between the IDs, so that the database will forcefully restrict you from deleting numbers that are already linked to words, or from adding words that don't have a number to match. However that could cause other issues, like if a number was added but no word, any following number+word combination would be out of sync. The word would be matched with the previous, orphaned, number.

Which brings us to a the more practical solution: a Foreign Key. Adding a column in the words table that references the ID of a number allows you to add numbers and words that will be matched to each other regardless of the status of the auto incremented PK. They can have vastly different PK values, but because the relationship between them depends on a FK rather than synchronicity PKs that doesn't matter.

This updated version of the previous example demonstrates how to do this:

CREATE TABLE `numbers` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `number` INTEGER NOT NULL,
    PRIMARY KEY (`id`)
) Engine=InnoDB;

CREATE TABLE `words` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `word` VARCHAR(50) NOT NULL,
    `number_id` INT NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY (`number_id`)
        REFERENCES `numbers`(`id`)
) Engine=InnoDB;

INSERT INTO `numbers`(`number`)
VALUES (0), (1), (1), (2), (3), (5), (8);

INSERT INTO `words`(`word`, `number_id`)
VALUE 
    ('Zero', 1), ('One', 2), ('One', 3), 
    ('Two', 4), ('Three', 5), ('Five', 6), 
    ('Eight', 7);

There are three important things to note there:

• First, the added Engine=InnoDB to the table definition. In MySQL, there are many different types of tables. The Engine specifies which type you want to use. In order to have the database enforce companionship, you must (currently) use the InnoDB type. In may cases the MyISAM type is the default, so this must be specified.
  
  
• Second, the added FOREIGN KEY ... REFERENCES ... clause in the `words` table. This defines the link between the tables. It tells MySQL that you want the 'number_id' column in the 'words' table to be linked to the 'id' field in the 'numbers' table. MySQL will then make sure that, when you add words, the number you are adding the word to does exist. If it doesn't, then it won't allow the INSERT. It also prevents you from deleting numbers that have words attached to them.
  
  
• And third, the updated INSERT statement for the words table. Note that now I'm not only inserting the word, but I'm also providing the ID of the number I want it associated with. In my example this is simply the 1-10 range, but in practice you'd have to insert the number first, fetch the new ID, and then insert the word with that ID. (It's not hard, though.)

The SELECT query would also have to be a bit different. Instead of joining the tables on the IDs, you join them on the "numbers.id" field and the "words.number_id" field.

mysql> SELECT
    ->     `numbers`.`number`,
    ->     `words`.`word`
    -> FROM numbers
    -> JOIN words
    ->     ON numbers.`id` = words.`number_id`;
+--------+-------+
| number | word  |
+--------+-------+
|      0 | Zero  |
|      1 | One   |
|      1 | One   |
|      2 | Two   |
|      3 | Three |
|      5 | Five  |
|      8 | Eight |
+--------+-------+

This type of relationship is what we call a "One-To-Many" ("1:N" for short) relationship. As the name indicates, this allows many rows in the table that has the FK definition to be linked to one row in the referenced table. In our example, this allows many words to be linked to each number. - If you'd prefer that not to be possible, you can fall back on the "One-To-One" relationship I mentioned before.