Re: Need help with (tricky?) select

In article <CoHu1F.5tv_at_nimno.wpa.com>, John Bartley <john_at_wpa.com> wrote:
>I think I've found the limit of my SQL-writing capabilities while trying
>to come up with a solution to this problem:
>I have a list of about 300 city names. I need to create a list of city
>pairs for input into a PC program that computes the highway miles between
>two cities. The output from this process will be fed back into a logistics
>package for use in scheduling truck deliveries. Suppose there were only
>three cities in the city table - Akron, Buffalo, Chicago. I'm trying to
>build the following output (there's a twist here, in that Akron to Buffalo
>is not considered the same as Buffalo to Akron.)
>FCITY FST TCITY TST
>============== === ============= ===
>Akron OH Buffalo NY
>Akron OH Chicago IL
>Buffalo NY Akron OH
>Buffalo NY Chicago IL
>Chicago IL Akron OH
>Chicago IL Buffalo NY
>Thanks a lot for any help!
>John Bartley
>john_at_wpa.com

This query should do the trick. I guess u were stumped because the concept of joining a table with itself is a lovely feature of relational databases and u were not used to this concept. In fact u could create a view with this statement. The description of the your problem will dicate that u create a table with this query and create a distance field and update it using a form.

select a.fcity fcity,a.fst fstate,b.fcity tcity,b.fstate tst from seed a,seed b where a.fcity!=b.fcity

U will have 300P2 permutations i.e 300*300-300 rows.

Hope this helps.

Thanks and Regards
Mahesh Vallampati
M.S. In EE
Dept.of Electrical Engineering,
Texas A & M University.
Ph:(409)845-6189
\\ In the Beginning there was Codd..... Received on Thu Apr 21 1994 - 00:10:06 CEST