Solved

SQL challenge

Posted on 1999-01-04
13
382 Views
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 ?
=========================
SELECT
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,
b.dept_station
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')
0
Comment
Question by:slok
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 7

Expert Comment

by:yoren
Comment Utility
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?

Yuval
0
 
LVL 3

Author Comment

by:slok
Comment Utility
I'm trying to get all the flight info.
It is possible to have up A->B->C->D.

0
 
LVL 9

Expert Comment

by:cymbolic
Comment Utility
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?
0
 

Expert Comment

by:T38Jet
Comment Utility
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...
0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
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;
 
DEPT_STATION ARRV_STATION
------------ ------------
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>/
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')
 
ITINERARY_ID DEPT_STATION ARRV_STATION
------------ ------------ ------------
           0 A            D
           1 A            B
             B            C
             C            D
           6 A            C
             C            D
 
6 rows selected.
 

0
 
LVL 7

Accepted Solution

by:
yoren earned 100 total points
Comment Utility
See my comment and accept if it answered your question.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:slok
Comment Utility
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.

Thanks.
0
 

Expert Comment

by:leehaw
Comment Utility
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?

0
 
LVL 3

Author Comment

by:slok
Comment Utility
Adjusted points to 100
0
 

Expert Comment

by:leehaw
Comment Utility
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...

0
 
LVL 7

Expert Comment

by:yoren
Comment Utility
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,

Yuval

0
 

Expert Comment

by:leehaw
Comment Utility
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.
>

0
 
LVL 3

Author Comment

by:slok
Comment Utility
thanks a lot
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now