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;
However, I can't find a way to identify the stops (S3.name) where transfers.
occur. Also, Rw.num and R2.company remain static. Any idea what I am doing
wrong here?
From: eem...@hotmail.com
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Edinburgh Buses
Date: Tue, 26 Aug 2014 11:03:07 -0400
Igor, thanks for a meaningful response. I am a neophyte at SQL, and learning it
on my own. The graphical approach to a solution is another methodology I will
be adding to my arsenal.
The reason why I was checking both names against stop B is that my intent was
to find the other route stops that connected to these stops. I am now aware
that these connections are not possible.
Errol Emden
> To: sqlite-users@sqlite.org
> From: i...@tandetnik.org
> Date: Mon, 25 Aug 2014 20:05:40 -0400
> Subject: Re: [sqlite] Edinburgh Buses
>
> On 8/25/2014 7:52 PM, Errol Emden wrote:
> > FROM stops astop
> > JOIN stops bstop ON bstop.name='Craiglockhart' OR bstop.name='Sighthill'
>
> Why are you checking two names against stop B, and none against stop A?
>
> You also need to check that route A and route B actually connect - that
> the end stop of one is the start stop of the other.
>
> I suggest you draw a picture, then write down all the conditions that
> need to be true to ensure that all the dots and lines connect to each
> other the right way.
> --
> Igor Tandetnik
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
