MYSQL Соединение нескольких таблиц по вертикали, чтобы дать отсортированную по дате историю

Question

Я создаю интерфейс для соединения таблиц и представления информации в Retool о бывших в употреблении продуктах, которые мы продаем от имени клиентов.

У меня есть несколько таблиц, которые относятся к одному продукту. В других таблицах содержится информация о процессе или статусе - например, отправлено на ремонт, продано, возвращено, продавец выплатил.

Я ищу таблицу, которая дает историю продукта, отсортированную по дате. Я могу ПРИСОЕДИНЯТЬСЯ ко всем таблицам и получить одну строку данных, но я ищу вертикальную таблицу, например:

      +--+-----------+---------------+
|id|Date       |Status         |
+--+-----------+---------------+
| 1| 2020-01-01|Booked in      |
+--+-----------+---------------+
| 1| 2020-01-04|Sent for repair|
+--+-----------+---------------+
| 1| 2020-02-10|Sold           |
+--+-----------+---------------+
| 1| 2020-02-28|Returned       |
+--+-----------+---------------+

Столбец статуса будет оператором CASE, определяемым информацией, которая присутствует в какой таблице.

На данный момент это больше похоже на

      +--+-----------+---------------+-----------+---------------+-----------+---------------+
|id|Date       |Status         |Date       |Status         |Date       |Status         |
+--+-----------+---------------+-----------+---------------+-----------+---------------+
| 1| 2020-01-01|Booked in      | 2020-01-01|Booked in      | 2020-01-04|Sent for repair|
+--+-----------+---------------+-----------+---------------+-----------+---------------+

Итак, есть ли способ объединить несколько таблиц по вертикали на основе одного идентификатора продукта.

Надеюсь, все это имеет смысл, если что-то нуждается в разъяснении, я постараюсь.

Заранее спасибо

Соответствующие таблицы / данные ниже:

      CREATE DATABASE StackOverflow_test;

USE StackOverflow_test;

CREATE TABLE    commission_sales
                (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    customer_id INT,
                    created_at TIMESTAMP DEFAULT NOW(),
                    product VARCHAR(20)
                    );
    
CREATE TABLE    sold_data
                (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    comm_no INT,
                    final_price DECIMAL(8,2),
                    sale_date DATE,
                    sale_ref VARCHAR(20),
                    created_at TIMESTAMP DEFAULT NOW(),
                    FOREIGN KEY (comm_no)
                        REFERENCES commission_sales(id)
                    );
    
    
CREATE TABLE    returned_from_buyer
                (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    comm_no INT,
                    returned_date DATE,
                    returned_notes VARCHAR(255),
                    FOREIGN KEY (comm_no)
                        REFERENCES commission_sales(id)
                    );
    
CREATE TABLE    returned_to_seller
                (   
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    comm_no INT,
                    returned_date DATE,
                    returned_notes VARCHAR(255),
                    FOREIGN KEY (comm_no)
                        REFERENCES commission_sales(id)
                    );
    
CREATE TABLE    additional_charges
                (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    comm_no INT,
                    amount DECIMAL(8,2),
                    notes VARCHAR(250),
                    paid BOOL DEFAULT 0,
                    created_at TIMESTAMP DEFAULT NOW(),
                    FOREIGN KEY (comm_no)
                        REFERENCES commission_sales(id)
                    );

И некоторые поддельные данные:

      INSERT INTO     commission_sales
                (id,customer_id,product,created_at)
        VALUES  (1,72,"Teddy Bear","2020-12-10")
                ;
    
INSERT INTO sold_data
                (comm_no,final_price,sale_date,sale_ref)
        VALUES (1,25.99,"2020-12-15","AGRJOWKO")
                ;
    
    
INSERT INTO returned_from_buyer
                (comm_no,returned_date,returned_notes)
        VALUES  (1,"2020-12-29","Broken")
        ;
    
INSERT INTO returned_to_seller
                (comm_no,returned_date,returned_notes)
        VALUES  (1,"2021-01-30","Customer is Idiot")
                ;
    
INSERT INTO     additional_charges
                (comm_no,amount,notes,paid,created_at)
        VALUES  (1,10,"repair",0,"2021-01-05")
        ;

Базовый JOIN помещает все данные в одну строку, но я хочу, чтобы данные отображались вертикально:

      SELECT * FROM commission_sales
JOIN sold_data ON commission_sales.id = sold_data.comm_no
JOIN additional_charges ON commission_sales.id = additional_charges.comm_no
JOIN returned_from_buyer ON commission_sales.id = returned_from_buyer.comm_no
JOIN Returned_to_seller ON commission_sales.id = returned_to_seller.comm_no
;

Answer 1

Я считаю, что вам следует создать ВИД под названием actions так.

      CREATE OR REPLACE VIEW actions AS
SELECT commission_sales.id, 
       returned_from_buyer.returned_date date,
       'returned_from_buyer' action, 
       returned_from_buyer.returned_notes notes
  FROM commission_sales
  JOIN returned_from_buyer ON commission_sales.id = returned_from_buyer.comm_no
  
 UNION ALL
  
SELECT commission_sales.id, 
       returned_to_seller.returned_date date,
       'returned_to_seller' action, 
       returned_to_seller.returned_notes notes
  FROM commission_sales
  JOIN returned_to_seller ON commission_sales.id = returned_to_seller.comm_no  

  UNION ALL
  
   SELECT commission_sales.id, 
       additional_charges.created_at date,
       'additional_charges' action, 
       additional_charges.notes notes
  FROM commission_sales
  JOIN additional_charges ON commission_sales.id = additional_charges.comm_no  ;

Это представление с операциями UNION ALL является своего рода операцией приведения типов. Он извлекает данные из этих трех таблиц и помещает их в общий формат. Представьте себе VIEW как виртуальную таблицу.

Затем вы можете получить из него свои данные следующим образом.

      SELECT * FROM actions ORDER BY id, date;

Или вы можете присоединить его к другим таблицам в вашей системе. Вот скрипка .