How to combine SQL queries that returns more than a row without using `RIGHT JOIN` or `FULL OUTER JOIN`?

user_14528213 0
Viewed 20

I'm trying to combine these two queries in SQLite3 without using RIGHT JOIN or FULL OUTER JOIN if possible since the ide my course is using doesn't support them.

SELECT stations.name FROM stations, journey WHERE stations.id = journey.start_station_id

and

SELECT stations.name FROM stations,journey WHERE stations.id = journey.end_station_id

I want the first column to be the name of the start station and the second column to be the name of the end station. I found a few similar questions like mine but I still can't get the result I want. Thanks in advance.

sqlite
1 Answers

If you put the two names on the same column, it might be as easy as this:

SELECT stations.name
FROM stations, journey
WHERE stations.id = journey.end_station_id
  OR stations.id = journey.start_station_id

However, if you needs both on different columns, you could do something like this:

SELECT
    stations.name AS start_station_name,
    NULL as end_station_name
FROM stations
WHERE stations.id = journey.start_station_id
UNION
SELECT
    NULL AS start_station_name,
    stations.name as end_station_name
FROM stations
WHERE stations.id = journey.end_station_id

Unfortunately, I couldn't think a way to put the two rows on the same row. For example, the GROUP BY strategy failed.

SELECT * FROM (
  SELECT
    stations.name AS start_station_name,
    NULL as end_station_name,
    '-' AS result.equal_name
  FROM stations
  WHERE stations.id = journey.start_station_id
  UNION
  SELECT
    NULL AS start_station_name,
    stations.name as end_station_name,
    '-' AS result.equal_name
  FROM stations
  WHERE stations.id = journey.end_station_id
) AS result
GROUP BY result.equal_name

However, if you ask me, it's too much complexity for getting 2 names. There should be a more clever solution to get 2 names and identifying which one is the start station, which may include using Back-End programming languages.

user_134193730
Related