Go Premium for a chance to win a PS4. Enter to Win


SQL challenge

Posted on 1999-01-04
Medium Priority
Last Modified: 2011-09-20
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')
Question by:slok
  • 4
  • 4
  • 3
  • +2

Expert Comment

ID: 1026188
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?


Author Comment

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


Expert Comment

ID: 1026190
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?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 1026191
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...

Expert Comment

ID: 1026192
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.


Accepted Solution

yoren earned 400 total points
ID: 1026193
See my comment and accept if it answered your question.

Author Comment

ID: 1026194
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.


Expert Comment

ID: 1026195
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?


Author Comment

ID: 1026196
Adjusted points to 100

Expert Comment

ID: 1026197
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...


Expert Comment

ID: 1026198
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,



Expert Comment

ID: 1026199
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.


Author Comment

ID: 1026200
thanks a lot

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question