Home>
I want to create a new view from two tables with MYSQL.

I want to create a new view (new_view) from two tables (table_a, table_b) as shown below, but I can't get the results I want.

[table a]

Date_a (pk) Value_a
20200602 1638
20200603 1630
20200604 1652
20200605 1619
20200608 1625
20200609 1619
20200610 1613

[table b]

Date_b (pk) Value_b
20200601 1000
20200607 1200
20200608 1500
20200611 1600

[new_view]

Date_a (pk) Value_a Date_b Value_b
20200602 1638 20200601 1000
20200603 1630 20200601 1000
20200604 1652 20200601 1000
20200605 1619 20200601 1000
20200608 1625 20200607 1200
20200609 1619 20200608 1500
20200610 1613 20200608 1500

[Rule]

  • Attach table_b based on table_a (JOIN)
  • Attach the past date and the latest Date_b and Value_b from the viewpoint of Date_a (MAX (Date_b) WHERE Data_a>Date_b)
What I tried

I've tried the following SQL, but I'm not getting the results I want.

SELECT Date_a, Value_a, Date_b, Value_b FROM table_a
JOIN table_b
ON (table_a.Date_a = (SELECT MAX (table_b.Date_b) from table_a where table_a.Date_a>table_b.Date_b))

The version is below.
MariaDB>select version ();
+ ----------------- +
| version () |
+ ----------------- +
10.2.10-MariaDB |
+ ----------------- +

(Addition)
The question was unclear, but what I want to do in the end is to create a view like [new_view].
It means that you don't know the SELECT statement that is the basis of it.

Here are the CREATE and INSERT statements for the table illustrated in the question.

CREATE TABLE table_a (
Date_a INTEGER,
Value_a INTEGER,
PRIMARY KEY (Date_a)
);

CREATE TABLE table_b (
Date_b INTEGER,
Value_b INTEGER,
PRIMARY KEY (Date_b)
);

INSERT INTO table_a VALUES
(20200602,1638),
(20200603,1630),
(20200604,1652),
(20200605,1619),
(20200608,1625),
(20200609,1619),
(20200610,1613);

INSERT INTO table_b VALUES
(20200601,1000),
(20200607,1200),
(20200608,1500),
(20200611,1600);

I am still inexperienced in using SQL, so I would appreciate your help.

  • Answer # 1

    In order to join table_a and table_b 1: 1 it is not possible to join directly, so it is necessary to create bridging information.

    SELECT Date_a, Value_a, Date_b, Value_b
    FROM table_a inner join (
              select table_a.Date_a, MAX (table_b.Date_b) max_Date_b
              FROM table_a left join table_b
                    ON table_a.Date_a>= table_b.Date_b
              group by table_a.Date_a
          ) map
          on table_a.Date_a = map.Date_a
          left join table_b
          on map.max_Date_b = table_b.Date_b


    Or, 1: Combine by many and narrow down by conditions

    select Date_a, Value_a, Date_b, Value_b
    FROM table_a left JOIN table_b
          ON table_a.Date_a>= table_b.Date_b
    where table_b.Date_b = (
            select max (Date_b) from table_b
            where Date_b<= table_a.Date_a
          )
       or table_b.Date_b is null