mysql - Find the routes involving two buses that can go from A to B -

question in sql sqlzoo: find routes involving 2 buses can go craiglockhart sighthill. show bus no. , company first bus, name of stop transfer, , bus no. , company second bus.

this code found, won't work:

    select distinct  a.num, a.company,               trans1.name ,  c.num,  c.company route join route b on (a.company = b.company , a.num = b.num) join ( route c join route d on (c.company = d.company , c.num= d.num)) join stops start on (a.stop = start.id) join stops trans1 on (b.stop = trans1.id) join stops trans2 on (c.stop = trans2.id) join stops end on (d.stop =  end.id)  start.name = 'craiglockhart' , end.name = 'sighthill'             ,  trans1.name = trans2.name  order a.num asc , trans1.name 

when have big problems that, best practice partition it. technically, buses departs craiglockhart should reach sighthill enough transfers, we'll restrict ourselves 1 transfer (because that's how problem's worded).

so basically, need find bus departs craiglockhart, , bus arrives @ sighthill, , need find intersecting stops between those.

the first 2 parts incredibly easy (and got part right) :

select distinct num , company route inner join stops on stop = id name='craiglockhart' 

and

select distinct num, company bus route inner join stops on stop = id name='sighthill' 

from there, have list of buses departs craiglockhart , buses arrives @ sighthill. remaining problem finding 2 intersect.

the initial part of solution easy; need fetch name of 2 buses, name of stop.

select distinct  r1.num nofrom, r1.company cofrom, name,r2.num noto, r2.company coto 

which means have query @ least 3 tables

from stops inner join route r1 on r1.stop = id inner join route r2 on r2.stop = id 

so now, gathered list of possible transfers, need filter out useless ones. 1 suboptimal way :

where exists(     select 1     route r3      inner join stops s1 on r3.stop = s1.id     s1.name='craiglockhart' , r3.num = r1.num , r3.company = r1.company)  , exists(     select 1     route r4      inner join stops s2 on r4.stop = s2.id     s1.name='sighthill' , r4.num = r2.num , r4.company = r2.company) 

so 1 way :

select distinct r1.num nofrom, r1.company cofrom, name, r2.num noto, r2.company coto stops inner join route r1 on r1.stop = id inner join route r2 on r2.stop = id exists(     select 1     route r3      inner join stops s1 on r3.stop = s1.id     s1.name='craiglockhart' , r3.num = r1.num , r3.company = r1.company) , exists(     select 1     route r4      inner join stops s2 on r4.stop = s2.id     s2.name='sighthill' , r4.num = r2.num , r4.company = r2.company) 

relevant sqlfiddle

one thing worth noting though used "where exists(...)" when exercise implies should using self joins. won't give whole answer if you're trying learn, have go @ , try convert self joins ;)