sqlzoo 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.

SELECT DISTINCT 
    a.num, 
    a.company, 
    stopsb.name, 
    c.num, 
    c.company 
FROM 
    route a 
JOIN 
    route b ON a.company = b.company AND a.num = b.num 
JOIN 
    stops stopsa ON a.stop = stopsa.id 
JOIN 
    stops stopsb ON b.stop = stopsb.id
JOIN 
    route c ON stopsb.id = c.stop
JOIN 
    route d ON c.company = d.company AND c.num = d.num 
JOIN 
    stops stopsc ON stopsc.id = c.stop 
JOIN 
    stops stopsd ON stopsd.id = d.stop 
WHERE 
    stopsa.name = 'Craiglockhart' 
    AND stopsd.name = 'Lochend' 
    AND stopsb.id = stopsc.id
ORDER BY a.num ASC, stopsb.name, c.num;