John

[JohnD]

Hi!

I am a beginner in SQL and trying to understand a few things. We have many values that stores into database, what I am trying to do is what I show in the attached image. 

Thanks for help

[Ingolme]

Assuming that all of the table have the same DateTime values and the same number of rows, the query is not too difficult. If the different tables have different date times it becomes more complicated.

The simple query would be like this:

INSERT INTO `NewTable` (`DateTime`, `Object1Value`, `Object2Value`, `Object3Value`)
  SELECT t1.`DateTime`, t1.`Object1Value`, t2.`Object2Value`, t3.`Object3Value`
  FROM `Table1` AS t1
  JOIN `Table2` AS t2 ON t2.`DateTime` = t1.`DateTime`
  JOIN `Table3` AS t3 ON t3.`DateTime` = t1.`DateTime`

This may be very slow depending on how many rows there are to copy from the tables.

[JohnD]

On 9/5/2020 at 8:01 AM, Ingolme said:

Assuming that all of the table have the same DateTime values and the same number of rows, the query is not too difficult. If the different tables have different date times it becomes more complicated.

The simple query would be like this:

INSERT INTO `NewTable` (`DateTime`, `Object1Value`, `Object2Value`, `Object3Value`)
  SELECT t1.`DateTime`, t1.`Object1Value`, t2.`Object2Value`, t3.`Object3Value`
  FROM `Table1` AS t1
  JOIN `Table2` AS t2 ON t2.`DateTime` = t1.`DateTime`
  JOIN `Table3` AS t3 ON t3.`DateTime` = t1.`DateTime`

This may be very slow depending on how many rows there are to copy from the tables.

Thank you so much for the answer, I will try this out when I get a little more time.

 

The DateTime values should be the same, I have a program that stores values exactly every hour and hopefully it should be the same all over the tables.

About rows, here will be a little problem. As I am adding objects to log and store to database, they will be installed on diffrent times. I have a few object up and running as we speak but more will be added in the future so the row count will not be the same for each table. That is why I want it to check the DateTime instead of a id number that will be diffrent for all tables, will this be a problem in the future you think?

I noticed that in my example picture I draw it looks for the ascending order, but I will be looking at the tables descending. Always want to see the latest values.

There will be many rows at the end so this might end up slow yes.

(Is there anyway to change the title? Little missleading. Somehow I wrote my name instead of an actual explaining title)