sql - Complex Subquery into Self Join from sqlzoo task 10 -

i trying solve task 10 http://sqlzoo.net/wiki/self_join

here select in select:

select  distinct astops.name, bbstops.name   route join route b on a.company=b.company , a.num = b.num               join stops astops on a.stop = astops.id               join stops bstops on b.stop = bstops.id astops.name = 'craiglockhart' , bstops.name in (   select  aastops.name      route aa join route bb on aa.company=bb.company                                            , aa.num = bb.num                 join stops aastops on aa.stop = aastops.id                 join stops bbstops on bb.stop = bbstops.id   bbstops.name = 'sighthill'   ) 

this sql code not work, because can't use table's name defined in inner 'in' select. solution change 'in' select subquery self join.

question how in example?

i believe answer close this:

select  astops.name, cstops.name   route join route b on a.company=b.company , a.num = b.num               join route c on b.company=c.company , b.num = c.num , b.stop = c.stop               join stops astops on a.stop = astops.id               join stops bstops on b.stop = bstops.id               join stops cstops on c.stop = cstops.id astops.name = 'craiglockhart' , cstops.name = 'sighthill' 

but missed here... it?

update:

here more detailed version of question:

there 2 tables: 1 route(num,company, stop)   2 stops(id, name).

so have table of bus routes, i.e. : bus number, company, , stop pass threw... , in select (http://pastebin.com/sq4vcry3) (let named query1) want figure out possible craiglockhart sighthill changing lanes twice.
asking: ok... can go somewhere craiglockhart... can 'somewhere' 'sighthill'? , query1 works! but… make sqlzoo accept answer… need show user not name of initial point (craiglockhart) name of final stop ('sighthill') first tried reference final point subquery as: bbstops.name (look http://pastebin.com/bbeltyrl) , did not work because can't reference alias subquery.

that's why… still thinking on how final stop name in result… decided join subquery query1 main select… result got http://pastebin.com/jdjdv5wm (let called query2)… query2 valid… not work because shows nothing… so… make mistake while converting query1 query2… question how make conversion properly?

set sql_big_selects=1;  select distinct s1.name, s2.name route r1      join stops s1 on r1.stop=s1.id , s1.name='sighthill'       join route r2 on r1.company=r2.company , r1.num=r2.num      join route r3 on r2.stop=r3.stop      join route r4 on r3.company=r4.company , r3.num=r4.num      join stops s2 on r4.stop=s2.id , s2.name='craiglockhart'