MySql - Linked list combination and store the route data

Question

I store data in mysql database as linked list interpretation. Linked List in SQL

Here is the data.

+------+------------+--------------+-----------+--------------------------+--------+----------+------------+------------+
| flag | sort_order | country_name | city_name | port_name                | voyage | vessel   | route_eta  | route_etd  |
+------+------------+--------------+-----------+--------------------------+--------+----------+------------+------------+
| FROM |          0 | Indonesia    | Jakarta   | Tanjung Priok            | N010   | INFINITY | 2020-11-03 | 2020-11-04 |
| FROM |          1 | Indonesia    | Semarang  | Tanjung Emas             | N010   | INFINITY | 2020-11-04 | 2020-11-06 |
| FROM |          2 | Indonesia    | Surabaya  | Tanjung Perak            | N010   | INFINITY | 2020-11-06 | 2020-11-07 |
| TO   |          3 | Taiwan       | Taipei    | Port of Taipei (Tamshui) | N010   | INFINITY | 2020-11-13 | 2020-11-13 |
| TO   |          4 | Taiwan       | Taichung  | Port of Taichung         | N010   | INFINITY | 2020-11-14 | 2020-11-14 |
| TO   |          5 | Taiwan       | Kaohsiung | Port of Kaohsiung        | N010   | INFINITY | 2020-11-15 | 2020-11-15 |
+------+------------+--------------+-----------+--------------------------+--------+----------+------------+------------+

Please pay attention to the flag column. For example, I want from Indonesia to go to Taiwan on a particular port by maintaining which route just passed. I need data with the following combinations:

flag    sort_order  country_name    city_name   port_name           flag    sort_order      country_name    city_name   port_name                   via
FROM            0   Indonesia       Jakarta     Tanjung Priok       TO          3           Taiwan          Taipei      Port of Taipei (Tamshui)    Tanjung Emas, Tanjung Perak
FROM            0   Indonesia       Jakarta     Tanjung Priok       TO          4           Taiwan          Taichung    Port of Taichung            Tanjung Emas, Tanjung Perak, Port of Taipei (Tamshui)
FROM            0   Indonesia       Jakarta     Tanjung Priok       TO          5           Taiwan          Kaohsiung   Port of Kaohsiung           Tanjung Emas, Tanjung Perak, Port of Taipei (Tamshui), Port of Taichung
                                                        
FROM            1   Indonesia       Semarang    Tanjung Emas        TO          3           Taiwan          Taipei      Port of Taipei (Tamshui)    Tanjung Perak
FROM            1   Indonesia       Semarang    Tanjung Emas        TO          4           Taiwan          Taichung    Port of Taichung            Tanjung Perak, Port of Taipei (Tamshui)
FROM            1   Indonesia       Semarang    Tanjung Emas        TO          5           Taiwan          Kaohsiung   Port of Kaohsiung           Tanjung Perak, Port of Taipei (Tamshui), Port of Taichung
                                                            
FROM            2   Indonesia       Surabaya    Tanjung Perak       TO          3           Taiwan          Taipei      Port of Taipei (Tamshui)    DIRECT
FROM            2   Indonesia       Surabaya    Tanjung Perak       TO          4           Taiwan          Taichung    Port of Taichung            Port of Taipei (Tamshui)
FROM            2   Indonesia       Surabaya    Tanjung Perak       TO          5           Taiwan          Kaohsiung   Port of Kaohsiung           Port of Taipei (Tamshui), Port of Taichung

If I want to process this data directly from the database side without a programming language like PHP, is it possible?

score 0 · Answer 1 · answered Nov 01 '20 at 20:36

Here is your solution, any questions?

CREATE TABLE PlaneRoutes(
    unique_id int NOT NULL AUTO_INCREMENT,
    route_id int NOT NULL,
    flag varchar(250),
    country_name varchar(250),
    city_name varchar(250),
    PRIMARY KEY(unique_id)
);


CREATE VIEW[PlaneRoutes_TO] AS
SELECT* FROM PlaneRoutes
WHERE flag = "TO"
CREATE VIEW[PlaneRoutes_FROM] AS
SELECT * FROM PlaneRoutes
WHERE flag = "FROM"


SELECT
a.route_id as `Route ID`,
a.country_name as `Flight from: Country`, a.city_name as `Flight from: City`,
b.country_name as `Flight to: Country`, b.city_name as `Flight to: City`

FROM[PlaneRoutes_FROM] as a
LEFT JOIN[PlaneRoutes_TO] as b
ON a.route_id = b.route_id
WHERE a.route_id = 124 AND b.route_id = 124

SS: https://prnt.sc/vbdeih

MySql - Linked list combination and store the route data

1 Answers1