mysql - SQLZoo 自连接教程

Question

我尝试按照提示使用自连接两次，但失败了。

Find the routes involving two buses that can go from Craiglockhart to Sighthill.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.

Hint: Self-join twice to find buses that visit Craiglockhart and Sighthill, then join those on matching stops.

我的代码:

select 
    a.num, a.company, stopsc.name, c.num, c.company 
from 
    route a 
    join route b
    -- From Craiglockhart to the transfer stop
    on (a.num=b.num and a.company=b.company)
    join route c
    -- to connect the transfer stop
    on (b.stop=c.stop)
    join route d
    -- From transfer stop to the final stop which is Sighthill
    on (c.num=d.num and c.company=d.company)
    join stops stopsa 
    on (a.stop=stopsa.id)
    join stops stopsb 
    on (b.stop=stopsb.id)
    join stops stopsc 
    on (c.stop=stopsc.id)
    join stops stopsd 
    on (d.stop=stopsd.id)
where 
    stopsa.name='Craiglockhart' 
and stopsd.name='Sighthill'
-- to delete the same route
and a.num!=c.num
order by a.num

我的回答有什么逻辑错误？

我知道 sqlselfjoin 上还有另一个答案但我想知道我哪一步出错了。

Answer 1

超出我的想象..如果这是您逐字的 SQL 语句，您需要删除 ** 注释 **。