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

Reply via email to