SQL challenge

I'm trying to retrieve data about a flight leg.
ie. A flight from Japan to New York can be a direct flight
or can have transition. So it will become pt A to pt B then
to pt. C.

Below is my sample code. Is there any problem ?
a.carrier_code, a.flight_num, a.period_from, a.period_to, a.DOP,
a.dept_station, a.dept_passenger_STD, b.arrv_station, a.arrv_passenger_STA,
FROM sia_segment a, sia_segment b
WHERE (b.dept_station = a.arrv_station AND
b.arrv_station = '$arrv_station' AND
a.dept_station = '$dept_station' AND
a.period_from <= '$flight_date' AND
a.period_to >= '$flight_date')
Who is Participating?
yorenConnect With a Mentor Commented:
See my comment and accept if it answered your question.
Which info are you trying to get? The whole flight schedule, or just a part of it? How many legs are possible? Is it possible to have a flight go A to B to C to D?

slokAuthor Commented:
I'm trying to get all the flight info.
It is possible to have up A->B->C->D.

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

would where b.arrv_station = '$arrv_station' AND
a.dept_station = '$dept_station' AND b.flight_num=a.flight_Num
and <the rest of your query>

work for you?  You should have the same flight_num in all matching segments shouldn't you?
What database are you using? Just wondering, because I don't think you can use the "$" character in your SQL statement under MS Access. The ? character matches any single alphabetic character, for instance B?ll finds ball, bell, and bill. The #      character matches any single numeric character.      For instance 1#3 finds 103, 113, 123. I think you'll need to use the ? character. If you're not using Access, you may double check which wildcards you can use in you SQL statement.

I hope this helps...
What you're asking for is not simple, for 2 reasons:

1. you want to "walk the tree" for as many legs as it takes to get to the destination
2. you only want to display trees (itineraries) whose last node is your final destination

It is far simpler and more efficient to do this within a procedure. However, if you insist on doing this within a single query, you'll want to use your database's equivalent of the "Connect by" clause. Below is an example in Oracle. If this is the type of thing you want, let me know and I'll try to explain it.

/* My table of flights. It has lots of different flights between cities A through F. You want to get
  to get from A to D. */
SQL> select * from yoren_tmp;
------------ ------------
A            D
A            B
C            D
B            C
A            E
D            F
A            C
C            E

/* The query below shows all the possible ways to get from A to D */
SQL> get tmp
  1  select itinerary_id, dept_station,arrv_station
  2  from
  3  (select rownum-level itinerary_id,
  4   dept_station,arrv_station
  5   from yoren_tmp
  6   where dept_station != '&&to'
  7   start with dept_station = '&&from'
  8   connect by prior arrv_station = dept_station
  9  ) a
 10  where exists
 11   (select * from
 12     (select rownum-level itinerary_id,
 13      dept_station,arrv_station
 14      from yoren_tmp
 15      where dept_station != '&&to'
 16      start with dept_station = '&&from'
 17      connect by prior arrv_station = dept_station
 18     ) b
 19    where a.itinerary_id = b.itinerary_id and
 20*   b.arrv_station = '&&to')
SQL> /
Enter value for to: D
old   6:  where dept_station != '&&to'
new   6:  where dept_station != 'D'
Enter value for from: A
old   7:  start with dept_station = '&&from'
new   7:  start with dept_station = 'A'
old  15:     where dept_station != '&&to'
new  15:     where dept_station != 'D'
old  16:     start with dept_station = '&&from'
new  16:     start with dept_station = 'A'
old  20:   b.arrv_station = '&&to')
new  20:   b.arrv_station = 'D')
------------ ------------ ------------
           0 A            D
           1 A            B
             B            C
             C            D
           6 A            C
             C            D
6 rows selected.

slokAuthor Commented:
Yoren : I would need some explaination on the code. FYI : I'm using Informix database
with their Web datablade.The web datablade is the tool that I use to build a front end query form.

Yes Yoren, as slok has pointed out, we're using INFORMIX Universal Server here. I have looked through the manual and I can't find anything on "connect by" or even recursive query. I believe that's a special SQL extension to Oracle. Would we be able to do all that fun stuff with standard ANSI(92?) SQL?

slokAuthor Commented:
Adjusted points to 100
I got confirmation from the Informix community that it doesn't have an equivalent command to Oracle's CONNECT BY. We have to code the logic by hand...

leehaw and slok,

Basically, Oracle's connect by is just a way of doing a recursive loop in a query. The best way to solve your problem is really in a procedure. The logic would be to take each flight and follow it to each possible end and take the ones that end at your destination. Since this may lead to many, many possibilities, you might want to limit the iterating to a maximum number of segments; 4, for example. So, if your procedure doesn't reach the destination within 4 legs, it moves on.

If you will have lots of hits to this database, you may want to consider creating pre-computed tables of departures and destinations. Something like this:

Table "Flight_Segments" is your main table: (Flight_ID,From,To,Departure_Time,Arrival_Time)

Table "Itineraries" columns: (Itinerary_ID,From,To,Departure_Time,Arrival_Time)
Table "Flight_Paths" columns: (Itinerary_ID,Sequence,Flight_ID)

Flight_Segments has info on each flight.
Itineraries has the From location and eventual destination.
Flight_Paths has the list of segments you take to complete the itinerary.

Hope this helps,


Thanks for your reply. Opens up a whole new SQL world for me. :)

Just a note, the "Parts Explosion" problem is slightly different to my "Connecting Flight" problem. For parts explosion, it's purely parent-child relationship. But for connecting flights, it's more complex since we have flights to and fro.

So, if the table contains this:

Depart     Arrive
------     ------
A          B
B          C
A          C
C          A
C          B
B          A

Then, using the parts explosion logic, when query for a connecting flight that starts with "A" and ends with "C", we might get:

A  C
A  B  C
A  B  A  C
A  C  A  C
A  B  C  A  C

Maybe a more complex SQL statement needed? Or am I imagining things?

Thanks, tata.

In article <77l3fl$ra61@webint.na.informix.com>,
  "Dennis Pimple" <dennisp@informix.com> wrote:
> As far as I can tell, Informix SQL doesn't have a direct equivalent to START
> WITH or CONNECT BY PRIOR. Your specific problem can be solved directly in a
> single SQL, with the caveat that we have to set a maximum number of hops.
> Here's my test for up to four hops (cribbed from Informix Guide to SQL
> Version 7.2 Tutorial page 5-29):

> create table route( start char(1), end char(1));

> insert into route values ("A", "B");
> insert into route values ("A", "C");
> insert into route values ("A", "D");
> insert into route values ("A", "E");
> insert into route values ("A", "G");
> insert into route values ("G", "H");
> insert into route values ("H", "I");
> insert into route values ("H", "E");
> insert into route values ("I", "E");
> insert into route values ("G", "B");
> insert into route values ("D", "F");
> insert into route values ("B", "C");
> insert into route values ("B", "E");
> insert into route values ("C", "E");

> select one.start, one.end, two.end, three.end, last.end
> from route one,
>      outer (route two,
>             outer (route three, outer route last))
> where one.start = "A"
> and last.end = "E"
> and one.end = two.start
> and two.end = three.start
> and three.end = last.start
> order by last.end, three.end, two.end, one.end

> Output:
> A     E
> A     B   E
> A     C   E
> A     D   F
> A     G   B   E
> A     B   C   E
> A     G   H   E
> A     G   B   C   E
> A     G   H   I   E

> Since Informix SQL doesn't have a way to reference a prior row, I don't
> think that a no-upper-limit solution is achievable. As your Oracle
> respondent points out, in 4GL, SPL, or other procedural language, this is a
> variation of the parts-explosion problem and readily (but not trivially)
> resolved. If you want to see it in 4GL, say "Please" and I'll write-and-post
> it.

slokAuthor Commented:
thanks a lot
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.