Re: Need help with (tricky?) select

From: David Pledger <davidp_at_meaddata.com>
Date: 19 Apr 1994 21:13:47 GMT
Message-ID: <2p1hib$c99_at_meaddata.meaddata.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.)

Try this...

Get things set up for a quick test

1> create table testit (city varchar(20), state char(2))
2> go
1> insert into testit values ("Akron","OH")
2> go

(1 row affected)
1> insert into testit values ("Buffalo", "NY") 2> go
(1 row affected)
1> insert into testit values ("Chicago", "IL") 2> go
(1 row affected)

Here is the query you want!

1> select T1.city, T1.state, T2.city, T2.state
2> from testit T1, testit T2
3> where T1.city != T2.city
4> and   T1.state != T2.state
5> go
 city                 state city                 state
 -------------------- ----- -------------------- -----
 Akron                OH    Buffalo              NY
 Akron                OH    Chicago              IL
 Buffalo              NY    Akron                OH
 Buffalo              NY    Chicago              IL
 Chicago              IL    Akron                OH
 Chicago              IL    Buffalo              NY

(6 rows affected)

Basically, what you want to do is to form the cartesian products of the two tables (same table twice) excluding those rows where the city and state match in both columns. The above query aliases table 'testit' to T1 and T2 which are treated as two separate tables in the query.

Regards,
David Pledger

-- 
+==============================+=============================================+
| David W. Pledger             | S T R A T E G I C   D A T A   S Y S T E M S |
| davidp_at_meaddata.com          |           PO Box 498, Springboro, OH  45066 |
| Custom Database Applications | Phone (513)748-2460, (800)253-5624 ext 2940 |

Received on Tue Apr 19 1994 - 23:13:47 CEST