You Can’t Get There from Here

November 1, 2002 Timothy Prickett Morgan

Hey, Howard:

I have a file on my AS/400 that contains flight segments. I need to identify all jobs by departing and final destination airport codes. The file is keyed by our job number and could have from one to four records per job number. It would be easy if every flight was direct, as the departure and destination codes would be on the same record. When the departure and final destination codes are on different records, that makes a search difficult.

The file details the date of a flight, the departure city, arrival city, flight number, sequence, and a code that indicates if that flight is its final destination. The flight numbers can change between legs of the journey, but a job number field is unique for a set of flights from origination to destination. As an example, I might want to find all flights departing JAX with a final destination of ORL. Oh, SQL Guru, can you show me the light?

— Alex

This is a real neat question, because we get to join a table to itself in order to answer the question. In fact, this is probably the best type of question to illustrate the concept of self-joins. First, we are going to need a table to hold the sample data. This table will have the fields FDATE for the flight date, JOBN for the job number, FLTN to hold the flight number, DPC for the departure city, DSC for the destination city, LEG to indicate if it is the final destination and SEQ to indicate the sequence of the flight in the JOB. Records in the table are uniquely keyed by JOBN and SEQ. Here are the SQL statements to create the test table and insert our sample data:

CREATE TABLE MCEIS.FLIGHTS 
  (FDATE  DATE NOT NULL,
   JOBN   INTEGER NOT NULL,
   FLTN   CHAR(4) NOT NULL,
   DPC    CHAR(3) NOT NULL,
   DSC    CHAR(3) NOT NULL,
   LEG    CHAR(1) NOT NULL,
   SEQ    SMALLINT NOT NULL,
   PRIMARY KEY (JOBN,SEQ));
   
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',1,'1321','JAX','ORL','F',1);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',2,'0012','JAX','ATL','C',1);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',2,'0012','ATL','SFX','C',2);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',2,'0091','SFX','JPL','C',3);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',2,'0456','JPL','ORL','F',4);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',3,'9876','ATL','MIA','C',1);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',3,'1132','MIA','ORL','C',2);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',3,'1132','ORL','JAX','C',3);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',3,'1132','JAX','ATL','F',4);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',4,'3213','IAD','LAX','F',1);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',5,'3320','IAD','BWI','C',1);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',5,'5555','BWI','ORD','C',2);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',5,'1254','ORD','DFW','C',3);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',5,'3213','DFW','LAX','F',4);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',6,'0212','JAX','ORL','C',1);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',6,'0412','ORL','ATL','C',2);
INSERT INTO MCEIS.FLIGHTS VALUES ('11/01/2002',6,'0591','ATL','PHX','F',3);

Here is a table of the information for reference:

At this point, we have a file called flights that is filled with one day’s worth of data regarding six jobs. First, let’s construct a query that finds all flights that originate from JAX and have a final destination of ORL. To do this, I want to create two sets from the table. The first set will contain all flights that depart JAX. A query that returns all JAX departing flights would be as follows:

SELECT FDATE, JOBN, DPC, DSC 
      FROM MCEIS.FLIGHTS 
      WHERE DPC='JAX';

That yields the following table:

The second set contains all flights that have a final destination of ORL. The query to find all flights that have a final destination of ORL is illustrated here:

SELECT FDATE, JOBN, DPC, DSC 
      FROM MCEIS.FLIGHTS 
      WHERE DSC='ORL' AND LEG='F'

That yields this table:

If you look at the results of query one, we have four flights that depart from JAX: flight numbers 1,2,3, and 6. Query two shows that we have two flights with final destinations of ORL: flight numbers 1 and 2. Now, we want to join the results of query one with query two. The following statement accomplishes that:

SELECT A.FDATE, A.JOBN, 
  A.FLTN AS ORIGIN_FN, B.FLTN AS DEST_FN, 
  A.DPC AS ORIGIN, B.DSC AS DEST
FROM MCEIS.FLIGHTS AS A 
      INNER JOIN MCEIS.FLIGHTS AS B 
    ON (A.JOBN=B.JOBN) 
WHERE B.LEG='F' AND A.DPC='JAX' AND B.DSC='ORL';

That yields this table of information:

Look at the FROM clause in the above statement and notice that we are selecting from FLIGHTS and naming it A and then joining to FLIGHTS and naming it B. You can join a table to itself in SQL, as long as you give it a unique name so you can tell SQL which columns you want. Next, look at the ON clause of the join. It is specifying that we want to join the sets when the A.JOBN is equal to the B.JOBN. Finally the WHERE clause indicates that we want records from the A table when the DPC=JAX, from the B table when the DSC=ORL, and the LEG=F. This is a perfect example of joining a table to itself to answer a query.

More Flights of Fancy

Now, let’s suppose you want to find all flights that go from JAX to ORL, but ORL does not have to be the final leg of the flight. You might think that you could just drop the B.LEG = F from the query. However, look at the results that action would generate:

Take a look at the route of flight 3. It leaves ATL and goes to MIA, then MIA to ORL. It then leaves ORL and goes to JAX and then leaves JAX and goes back to ATL. This flight departs from JAX and it arrives in OLR. However, it never arrives at ORL after leaving JAX. In order to ensure that you see only flights departing JAX and arriving ORL, you need to execute the following query:

SELECT A.FDATE, A.JOBN, 
  A.FLTN AS ORIGIN_FN, B.FLTN AS DEST_FN, 
  A.DPC AS ORIGIN, B.DSC AS DEST
FROM MCEIS.FLIGHTS AS A 
INNER JOIN MCEIS.FLIGHTS AS B 
    ON (A.JOBN=B.JOBN) 
WHERE A.DPC='JAX' AND B.DSC='ORL' AND A.SEQ<=B.SEQ;

That query yields the following information:

Because we quantified that the A.SEQ should be less than or equal to the B.SEQ we have eliminated flight 3 from the result set. Remember, the SEQ field tells the sequence of the leg. Now, if we wanted to find only direct flights from JAX to ORL, we simply change the above query to be A.SEQ=B.SEQ. This would result in flights 1 and 6, which are the only flights that can take us from JAX to ORL with no hops in between.

Retrieving the Routes

What if we wanted to find the routes for all flights that depart JAX and arrive ORL. The IN clause allows us to search for records that have a value of a field in a set of values. The set of values can be an expression or it can be the result of an SQL statement. The following statement lists the routes of all flights that leave JAX and arrive (at some point) in ORL:

SELECT * 
FROM MCEIS.FLIGHTS 
WHERE JOBN IN 
      (SELECT A.JOBN 
      FROM MCEIS.FLIGHTS AS A 
      INNER JOIN MCEIS.FLIGHTS AS B 
    ON (A.JOBN=B.JOBN) 
      WHERE A.DPC='JAX' AND B.DSC='ORL' AND A.SEQ<=B.SEQ)
ORDER BY JOBN, SEQ;

SQL will execute the query inside the IN clause to get a set of job numbers. It will then join the set of job numbers to the FLIGHTS table and sequence that result set by the JOBN and SEQ fields. The results look like this table:

Calculating Hops

Finally, here is one other interesting piece of information you can gather when looking at the set: the number of stops required before you reach the destination. To calculate this, simply subtract the B.SEQ from the A.SEQ, as illustrated in the following query:

SELECT A.FDATE, A.JOBN, 
  A.FLTN AS ORIGIN_FN, B.FLTN AS DEST_FN, 
  A.DPC AS ORIGIN, B.DSC AS DEST, 
  B.SEQ-A.SEQ AS STOPS
FROM MCEIS.FLIGHTS AS A 
  INNER JOIN MCEIS.FLIGHTS AS B 
  ON (A.JOBN=B.JOBN)
WHERE A.DPC='JAX' AND B.DSC='ORL' AND A.SEQ<=B.SEQ;

That action yields this table of information:

The following query displays each flight’s departure city and final destination city and the number of stops on the flight:

SELECT A.JOBN, A.DPC AS ORIGIN, 
  B.DSC AS DEST, B.SEQ-A.SEQ AS STOPS
FROM MCEIS.FLIGHTS AS A INNER JOIN MCEIS.FLIGHTS AS B 
  ON (A.JOBN=B.JOBN)
WHERE A.SEQ=1 AND B.LEG='F';

It is displayed as follows:

The A set includes only records that have a SEQ = 1, (start of JOB), and the B set includes only records that have a LEG of F (the final destination).

Alex, thanks for such a good question, which allowed me to illustrate the power of self joins. I think that most of the readers will be able to find ways to use this technique on their own data.

— Howard

Howard F. Arner, Jr. is a writer and consultant for Client Server Development, Inc. You can purchase Howard’s book, iSeries and AS/400 SQL at Work from www.sqlthing.com/books or go to www.sqlthing.com to learn more about using SQL on the AS/400.

Sponsored By RJS SOFTWARE SYSTEMS

Implement Document Imaging on your iSeries-AS/400 in 30 minutes or less Image Server/400 is a Web browser-based document image management system for the iSeries. Documents can be quickly scanned and stored in IFS folders, and then located and retrieved for viewing via any Web browser. Integrate with other iSeries-AS/400 applications. Visit us at COMMON, Booth 418, call us at 888-RJS-SOFT, or download a FREE fully functional demo from our Web site at www.rjssoftware.com