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