SQlZOO中self join最后一题难点解析

先贴题目

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.

翻译：列出从Craiglockhart到lochend的换乘选项，需要的5个内容分别是第一辆车号，哪家公司运营，换乘站，第二辆车号，哪家公司运营。

难点：用一个self join只能查询到直达巴士，需要用两个及以上才能查询到换成巴士

答案如下

select a.num,a.company,stopb.name,d.num,d.company
from route a 
join route b on a.company=b.company and a.num=b.num
join stops stopa on (a.stop=stopa.id)
join stops stopb on (b.stop=stopb.id),
route c join route d on (c.company = d.company and c.num= d.num)
	join stops stopc on (c.stop = stopc.id)
	join stops stopd on (d.stop = stopd.id)
where stopa.name='Craiglockhart' and stopd.name='Lochend' AND stopb.id = stopc.id
order by a.num,stopb.name,c.num