Goodly Folks, I am confounded by the required solution to the following problem:
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.
The database schema is as follows:
stops(id, name); route(num,company,pos, stop)
route.stop is the foreign key for stops.id and is numeric.
num is the route number, an alphanumeric string.
name is the name of stops.id, e.g. id 149 is 'London Road'.
My attempt does not provide the answer:
SELECT a.num, a.company, astop.name, b.num, b.company
FROM stops astop
JOIN stops bstop ON bstop.name='Craiglockhart' OR bstop.name='Sighthill'
JOIN route a ON a.stop=bstop.id
JOIN route b ON b.stop=astop.id;
The output should look like the following:
numcompanynamenumcompany4LRTLondon Road34LRT4LRTLondon Road35LRT4LRTLondon
Road65LRT4LRTLondon RoadC5SMT4LRTPrinces Street3LRT4LRTPrinces Street3ALRJThe
approach I am told is to self-join twice to find buses that visit
Craiglockhart and Sighthill, then join those on matching stops.
I have no idea how to do this and would like to know how to do this as
well as to find an even easier way to solve this problem.
Your assistance will be highly appreciated. Be well.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
