Re: Need help with (tricky?) select
Date: 19 Apr 1994 16:42:13 GMT
Message-ID: <2p11l5$5gd_at_k2.San-Jose.ate.slb.com>
In article <CoHu1F.5tv_at_nimno.wpa.com>, john_at_wpa.com (John Bartley) writes:
|> 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
|>
|> (The "seed" table contains only the 'fcity' and 'fst' columns - I need to
|> create the pairs as shown.)
|>
|> I'm hoping that this is trivially simple for someone who writes this stuff
|> every day. It has been (apparently) too many years since I took Oracle
|> training classes, and I'm having trouble conceptualizing how to approach
|> this. Can anyone take pity on me and give me a few clues? :-)
|>
|> Thanks a lot for any help!
|>
|> John Bartley
|> john_at_wpa.com
|>
|> (please reply by e-mail if possible - my news feed is way behind.)
SQL> create table cities (tcity varchar(10), tst char(2));
Table created.
SQL> insert into cities values ('Akron','OH');
1 row created.
SQL> insert into cities values ('Chicago','IL');
1 row created.
SQL> insert into cities values ('Buffalo','NY');
1 row created.
SQL> select a.tcity, a.tst, b.tcity, b.tst
2 from cities a, cities b
3 order by a.tcity;
TCITY TS TCITY TS ---------- -- ---------- -- Akron OH Akron OH Akron OH Chicago IL Akron OH Buffalo NY Buffalo NY Akron OH Buffalo NY Chicago IL Buffalo NY Buffalo NY Chicago IL Akron OH Chicago IL Buffalo NY Chicago IL Chicago IL
9 rows selected.
SQL> l
1 select a.tcity, a.tst, b.tcity, b.tst
2 from cities a, cities b
3 where a.tcity != b.tcity
4 and a.tst != b.tst
5 order by a.tcity
TCITY TS TCITY TS ---------- -- ---------- -- Akron OH Chicago IL Akron OH Buffalo NY Buffalo NY Akron OH Buffalo NY Chicago IL Chicago IL Akron OH Chicago IL Buffalo NY
6 rows selected. Received on Tue Apr 19 1994 - 18:42:13 CEST