MySQL on multiple tables to compare

Question

Support I have multiple tables (X,Y,Z) of the exact same structure. The tables have a DATETIME field and a DATA field. I want to extract and compare DATA values based on the same DATETIME for all tables. Assuming all tables have the same DATETIME values but different data. What would the SQL statement be?

I can't get it to work. Here are tables:

Table: A

DATETIME | HIGH | LOW

01:00 | 0.1 | 2.4

02:00 | 0.2 | 3.0

03:00 | 0.8 | 0.7

04:00 | 1.3 | 1.1

Table: B

DATETIME | HIGH | LOW

01:00 | 0.1 | 2.4

02:00 | 0.2 | 3.0

03:00 | 0.8 | 0.7

04:00 | 1.3 | 1.1

Table: C

DATETIME | HIGH | LOW

01:00 | 0.1 | 2.4

02:00 | 0.2 | 3.0

03:00 | 0.8 | 0.7

04:00 | 1.3 | 1.1

Structure for all tables are the same. I want to retrieve data from all tables where their datetime are same.

Answer 1

I'll post a new answer, seeing that your question changed. You need to perform a join between those tables:

SELECT * FROM
(A JOIN B ON A.datetime = B.datetime)
JOIN C ON B.datetime = C.datetime

That will create a table with columns:

datetime | A.high | A.low | B.high | B.low | C.high | C.low

Answer 2

This sounds like a case for the JOIN operator.

SELECT * FROM X JOIN Y ON X.datetime = Y.datetime JOIN Z on Y.datetime = Z.datetime

This should give you rows with one datetime column and X.data, Y.data and Z.data columns.