Search code examples
mysqldatabasemysql-workbenchcreate-table

Database CREATE TABLE for 2 similar tables

I have the following information in 2 *.CSV files. There are many more rows but I have included a small set here as an example.

file_id descrip Date file_heat_value_1 1 ABC 2015-02-11 1.02500 1 ABC 2014-11-19 0.85500 1 ABC 2014-05-22 17.20 2 DEF 2014-08-20 1.3700 2 DEF 2014-05-21 15.5500 2 DEF 2013-04-07 77.800 3 XYZ 2012-02-12 0.14400 3 XYZ 2013-02-12 0.97600

file_id descrip Date file_heat_value_2 1 ABC 2014-12-01 42.01 1 ABC 2013-04-02 11389.90 2 DEF 2014-12-03 0.87 2 DEF 2014-12-04 55.36 2 DEF 2014-07-04 143.29 3 XYZ 2012-11-05 77.90.02 3 XYZ 2013-07-08 34344.91

I need to add these 2 *.CSV files as tables in a MySQL database. I am not sure if I need 2 tables or one table in the database.

I cannot combine the *.CSV files together because the dates do not match. I will be using MySQL Workbench to load the *.CSV files into the 2 tables.

What I would like to do is this:

  1. Create a database:

CREATE schema temperat_monitor

  1. Create 2 tables

  2. Run the following query:

SELECT MAX(file_heat_value_1) AS HOT_Temperature, MIN(file_heat_value_1) AS Cold_Temperature, MAX(file_heat_value_2) AS HOT_Indoor_Temperature FROM table_1 INNER JOIN table_2 ON table_1.file_id = table_2.file_id

What would be the best way for me to create the 2 tables in MySQL?

EDIT: My attempt at using the MySQL Workbench File import wizard (6.3.1) is shown in the screenshot below.Image here. I have created a database and chosen to USE it. But this error comes up. Could you please let me know why this is appearing?

Solution

  • I would use one table and use a primary key with no business value, e.g.

    id INT AUTO_INCREMENT PRIMARY KEY

    in your create table statement.

    You'd end up with data similar to:

    id  file_id descrip Date       file_heat_value_1 
1   1       ABC     2015-02-11     1.02500 
2   1       ABC     2014-11-19     0.85500 
3   1       ABC     2014-05-22    17.20 
4   2       DEF     2014-08-20     1.3700 
5   2       DEF     2014-05-21    15.5500 
6   2       DEF     2013-04-07    77.800 
7   3       XYZ     2012-02-12     0.14400 
8   3       XYZ     2013-02-12     0.97600
9   1       ABC     2014-12-01    42.01 
10  1       ABC     2013-04-02 11389.90
11  2       DEF     2014-12-03     0.87
12  2       DEF     2014-12-04    55.36
13  2       DEF     2014-07-04   143.29
14  3       XYZ     2012-11-05 77.90.02
15  3       XYZ     2013-07-08 34344.91

    You can then select the data form this one table. Post another question if you need help with the select statement for that.