On 8/26/2014 5:39 PM, Errol Emden wrote:
I have taken a different approach to the solution to this problem. Instead of 
paring the self-joins I have cross-joined the tables in pairs as follows:
SELECT distinct R1.num, R1.company, S1.name, R2.num, R2.company
FROM stops S1, stops S2, route R1, route R2
WHERE S1.name='Craiglockhart' AND S2.name='Sighthill'
     AND R1.stop=S1.id AND R2.stop=S2.id;

Actually, it's more complicated. I just realized that routes may have multiple stops: there's a row in the "route" table for each stop on the route, not just one for each route. Bad design by the way; you may want to normalize it better. For one thing, you have to repeat route number and company name for each stop. You probably want routes(routeNum, company) and stopsOnRoute(routeNum, stopId, pos).

Anyway, you need to join with route four times - once for each stop on each route (there are three stops we care about, but the middle one is visited by two routes). Something like this:

SELECT StartOfR1.num, StartOfR1.company, EndOfR2.num, EndOfR2.company
FROM stops Start, stops Finish, route StartOfR1, route EndOfR1, route StartOfR2, route EndOfR2
WHERE
   Start.name='Craiglockhart' AND Finish.name='Sighthill'
   AND StartOfR1.id = Start.id  -- R1 actually visits Start
   AND EndOfR1.num = StartOfR1.num  -- two stops on the same route
   AND EndOfR1.id = StartOfR2.id -- R2 starts where R1 ends
   AND EndOfR1.num != StartOfR2.num -- R1 and R2 are not the same route
   AND EndOfR2.num = StartOfR2.num  -- two stops on the same route
   AND EndOfR2.id = Finish.id  -- R2 actually visits Finish
;

I'm not quite sure what role stops.pos field plays. If it's somehow significant, working it into the query is left as an exercise for the reader.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to