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
