SQLZOO-selfjoin

题目：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.
表1：

stops	Value
id
name

表2：

route	Value
num
company
pos
stop

解题思路：
1.首先应该对问题进行解读：查找涉及两条可以从Craiglockhart到Sighthill的巴士的路线。显示巴士号码 第一辆巴士的公司和公司，中转站的名称以及巴士号 和第二辆巴士的公司。
2.将问题进行分快来解读，从所有出发的公共汽车Craiglockhart最终都应该Sighthill经过足够的换乘，但是此处只考虑换乘一次。因此找到起点为Craiglockhart的车次和终点为Sighthill的车次。
起点为Craiglockhart的代码：

SELECT DISTINCT a.num, a.company, y.name 
     FROM route a JOIN route b ON (a.num = b.num and a.company = b.company) 
                  JOIN stops x ON x.id = a.stop 
                  JOIN stops y ON y.id = b.stop 
     WHERE x.name = 'Craiglockhart' AND y.name <> 'Craiglockhart'

终点为Sighthill的代码

SELECT x.num, x.company, sy.name 
     FROM route x JOIN route y ON (x.num = y.num and x.company = y.company) 
                  JOIN stops sx ON sx.id = x.stop 
                  JOIN stops sy ON sy.id = y.stop 
     WHERE sx.name = 'Lochend' AND sy.name <> 'Lochend'

然后使用表格stops来作为链接，从上述两个位置找到共享的站点，因此可以得到完整代码如下：

SELECT S.num, S.company, S.name, T.num, T.company 
FROM 
    (SELECT DISTINCT a.num, a.company, y.name 
     FROM route a JOIN route b ON (a.num = b.num and a.company = b.company) 
                  JOIN stops x ON x.id = a.stop 
                  JOIN stops y ON y.id = b.stop 
     WHERE x.name = 'Craiglockhart' AND y.name <> 'Craiglockhart'
)S

JOIN

    (SELECT x.num, x.company, sy.name 
     FROM route x JOIN route y ON (x.num = y.num and x.company = y.company) 
                  JOIN stops sx ON sx.id = x.stop 
                  JOIN stops sy ON sy.id = y.stop 
     WHERE sx.name = 'Lochend' AND sy.name <> 'Lochend'
    )T

ON (S.name = T.name)
ORDER BY S.num, S.name, T.num