Igor, I can't thank you enough for getting my head 'out of the box'. Your
insightful remarks and assistance are highly appreciated. I made the following
(underlined) changes to your script to include the transfer stops (Xfer.name):
SELECT distinct StartOfR1.num, StartOfR1.company, Xfer.name xfer_name,
EndOfR2.num, EndOfR2.company
FROM stops Start, stops Xfer, stops Finish, route StartOfR1, route EndOfR1,
route
StartOfR2, route EndOfR2
WHERE
Start.name='Craiglockhart' AND Finish.name='Sighthill'
AND StartOfR1.stop= Start.id -- R1 actually visits Start
AND EndOfR1.num = StartOfR1.num -- no transfer on the same route
AND EndOfR1.stop= StartOfR2.stop -- R2 starts where R1 ends
AND EndOfR1.num != StartOfR2.num -- R1 and R2 are not the same route
AND EndOfR1.stop = Xfer.id -- R1 changes to R2
AND EndOfR2.company = StartOfR2.company -- R1 changes bus to R2
AND EndOfR2.num = StartOfR2.num -- two stops on the same route
AND EndOfR2.stop = Finish.id -- R2 actually visits Finish;
Igor, because of your help, I was able to follow your succinct logic and
complete this very difficult and complicated exercise (for me :-)). Thank you
for your mentorship. You have been added to the other 2 stars on this site. Be
well.
Errol Emden
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
