CODEWITHSUNDEEP
sqlsqlite
chick3n0x07CC
chick3n0x07CC

Reputation: 798

How to combine two columns one below the other in SQLite?

I have a query in SQLite to get all the stations in a train route that, at the end, lets me with two columns named ORI_STATION_ID and ORIG_STATION_ID, like in the image:

enter image description here

This is my current query (ORDER_COL is not shown in the result table, it is only used for ordering):

SELECT table_3.ORI_STATION_ID, table_3.DEST_STATION_ID
    FROM table_1
    INNER JOIN table_2
    ON table_2.ID = table_1.ID
    INNER JOIN table_3
    ON table_3.ID = table_2.ID
    WHERE table_1.CODE = "DINO"
    ORDER BY ORDER_COL ASC

I want to get all the stations in the train route, from the first to the last one. Here, the first is the one with ID 1 and the last has ID 19.

I could take the first column and ready to go, but then I would miss the last station. If I took the second column, I would miss the first station.

What I want to do: combine the two columns in one putting one below the other and remove the duplicated stations, so my ALL_STATIONS_COLUMN would look like:

| ALL_STATIONS_COLUMN |
-----------------------
|          1          |
|          2          |
|          9          |
|         10          |
|         11          |
|         19          |

I have seen in other posts how to use joins to combine tables or CONCAT (but seems it doesn't suit for SQLite) but didn't found the proper way.

Upvotes: 0

Views: 567

Answers (2)

Shawn
Shawn

Reputation: 52344

Sounds like a case for a recursive CTE (Example just uses your final route table; I'm not going to try to come up with three tables worth of data; easy to extend the following with the query that computes that route as another CTE):

WITH cte AS
 (SELECT 1 AS all_stations_column, 1 AS stop
  UNION
  SELECT r.dest_station_id, cte.stop + 1
  FROM route AS r
  JOIN cte ON r.ori_station_id = cte.all_stations_column)
SELECT all_stations_column FROM cte ORDER BY stop;

giving

all_stations_column
-------------------
1
2
9
10
11
19

dbfiddle example

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

A union query would be one option here:

SELECT t3.ORI_STATION_ID AS ALL_STATIONS_COLUMN
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.ID = t1.ID
INNER JOIN table_3 t3 ON t3.ID = t2.ID
WHERE t1.CODE = 'DINO'
UNION
SELECT t3.DEST_STATION_ID
FROM table_1 t1
INNER JOIN table_2 t2 ON t2.ID = t1.ID
INNER JOIN table_3 t3 ON t3.ID = t2.ID
WHERE t1.CODE = 'DINO'
ORDER BY ALL_STATIONS_COLUMN;

This kills two birds at the same time (splat), because the union brings together the origin and destination station IDs, while also removing duplicate values should they occur between the two sets.

Upvotes: 2

Related Questions