MYSQL Joining multiple Tables vertically to give Date sorted history

I am building an interface to connect tables and present information in Retool about used products we are selling on behalf of customers.

I have several tables that all relate back to a single product. The other tables are information on a process or status - ie, Sent for repair, Sold, Returned, seller paid out.

I am looking to get a table that gives a product history sorted by date. I can JOIN all the tables and get a single row of data but I am looking for a vertical table like:

+--+-----------+---------------+
|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       |
+--+-----------+---------------+

The status column would be a CASE statement determined by the information that was present in which table.

At the moment its more like

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

So in summary, is there a way to join several tables Vertically based upon a single product ID.

I hope this all makes sense, if anything needs clarification, I will do my best.

Thanks in Advance

Relevent tables/data below:

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)
                    );

And some Fake Data:

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")
        ;

A basic JOIN puts all the data on one row, but I want the data displayed vertically:

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
;

I believe you should create a VIEW called actions like this.

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  ;

This view with UNION ALL operations in it is a sort of typecasting operation. It extracts the data from those three tables and puts them into a common format. Think of the VIEW as a virtual table.

Then you can retrieve your data from it like this.

SELECT * FROM actions ORDER BY id, date;

Or you can join it to other tables in your system. Here is a fiddle.