문제 이해

https://sqlzoo.net/wiki/Self_join

10. Find the routes involving two buses that can go from Craiglockhart to Lochend.
    Show the bus no. and company for the first bus, the name of the stop for the transfer,
    and the bus no. and company for the second bus.

데이터 이해

해당 문제는 우선 데이터에 대한 이해가 선행되어야 한다.
route 테이블과 stops 테이블이 있는데,
stops 테이블은 버스 정류소(stop)에 대한 id와 name이 있는 테이블이니 빠르게 넘어가도록 하자.

route 테이블은 어느 버스 회사(company)의 특정한 버스 노선(num)이 지나는 정류소(stop)들을 입력한 테이블이다.
하나의 버스 노선이 여러 개의 정류소를 지나게 되므로 동일한 num에 다수의 stop이 할당되어 여러 행이 존재하게 된다.

문제 이해

이 문제는 A -> (B ->) C로 이동하는 경로를 구하는 문제이다.
단, A와 C는 주어지지만 B는 주어지지 않는다.
즉 중간에 한 번 버스를 환승해야 하는 것이다.

SELECT 대상은 1번 버스의 num과 company, 환승할 정류소 이름(stop.name), 2번 버스의 num과 company이다.

참고로 문제에는 언급되어 있지 않지만 정렬 조건은 1번 버스 num, 환승 정류소 이름, 2번 버스 num 순서이다 (num이 int가 아닌 str로 입력되어 있는지 10 - 27 - 4 - 45 ... 순서로 정렬된다)

풀이 구상 및 쿼리 작성

해당 문제는 출발지에서 출발해 경유지를 지나 도착지로 향하는 버스의 경우의 수를 구하는 문제이다
처음에는 한 번에 삼중 조인을 수행하려 했으나, self join을 3번 처리하게 되면 ON절이 서로 중복되면서 2개 이상의 조건을 걸 수 없게 되었다.

그러므로 A-B와 B-C의 2가지 경우를 각각 SELF JOIN으로 처리한 뒤, 이를 다시 한 번 JOIN하는 방식으로 진행하였다.

출발지가 Craiglockhart인 경우

route의 SELF JOIN을 통해서 조건에 맞는 경우의 수를 전부 구할 수 있다

route는 특정 버스 노선의 모든 경우의 수를 포함하므로
INNER JOIN과 company와 num가 일치한다는 ON절 조건을 통해 SELF JOIN을 수행할 수 있다.

출발지 조건은 WHERE절에서 적어주도록 한다.

SELECT
  ra.num,
  ra.company,
  sa.name,
  rb.stop,
  sb.name as "stop_name"
FROM
  route ra
  INNER JOIN route rb ON ra.company = rb.company
  AND ra.num = rb.num
  INNER JOIN stops sa ON ra.stop = sa.id
  INNER JOIN stops sb ON rb.stop = sb.id
WHERE
  1 = 1
  AND sa.name = 'Craiglockhart'
  AND sb.name != 'Craiglockhart'

도착지가 Lochend인 경우

앞선 경우와 마찬가지이며, 도착지 조건만 작성해주면 된다.

SELECT
  rb.stop,
  rc.num,
  rc.company,
  sc.name
FROM
  route rb
  INNER JOIN route rc ON rb.company = rc.company
  AND rb.num = rc.num
  INNER JOIN stops sb ON rb.stop = sb.id
  INNER JOIN stops sc ON rc.stop = sc.id
WHERE
  1 = 1
  AND sc.name = 'Lochend'
  AND sb.name != 'Lochend'

경유지를 지나는 경로 구하기

앞서 작성한 2개의 쿼리를 CTE로 처리하고
이 둘을 각각 불러와서 3번째 JOIN을 수행한다.
조인 조건은 "1번의 도착지 = 2번의 출발지"로 작성한다

여담으로 해당 문제에서는 불필요하긴 했지만,
2개의 버스를 탑승해야 함으로 1번과 2번의 버스 노선이 다르다는 조건도 슬쩍 끼워넣었다.

WITH
  find_route_a AS (
    SELECT
      ra.num,
      ra.company,
      sa.name,
      rb.stop,
      sb.name as "stop_name"
    FROM
      route ra
      INNER JOIN route rb ON ra.company = rb.company
      AND ra.num = rb.num
      INNER JOIN stops sa ON ra.stop = sa.id
      INNER JOIN stops sb ON rb.stop = sb.id
    WHERE
      1 = 1
      AND sa.name = 'Craiglockhart'
      AND sb.name != 'Craiglockhart'
  ),
  find_route_b AS (
    SELECT
      rb.stop,
      rc.num,
      rc.company,
      sc.name
    FROM
      route rb
      INNER JOIN route rc ON rb.company = rc.company
      AND rb.num = rc.num
      INNER JOIN stops sb ON rb.stop = sb.id
      INNER JOIN stops sc ON rc.stop = sc.id
    WHERE
      1 = 1
      AND sc.name = 'Lochend'
      AND sb.name != 'Lochend'
  )
SELECT
  a.num as "departure_num",
  a.company as "departure_company",
  a.stop_name as "transfer_stop",
  b.num as "arrival_num",
  b.company as "arrival_company"
FROM
  find_route_a a
  INNER JOIN find_route_b b ON a.stop = b.stop
  AND a.num != b.num
ORDER BY
  departure_num,
  transfer_stop,
  arrival_num;