Hi,

Here I attach my test case as a zip file. The three files in the zip have self explanatory names. However, for you to better understand the select, I have the following short description.

The three tables are part of a real flight info system used in production.

1. PFIFlight: flight info

2. PFILanded: landed info.

One landed may have one or two related flights. Only arrival, only departure, or both arrival and departure. When the landed consists of only one arrival, or only one departure, the sample select have the concern of dynamically merging flights by using aircraft registration no, airline, landed airport, and an interval of 24 hours. However, there is one problem. If the same aircraft lands twice and then departs only one time, I have to prevent the first landing to merge with the departure flight. The same would be true if there were two departures. Only the first departure would be dynamically linked with the second landing. Let me try to draw it:

Arr_1   Arr_2    Dep_1    Dep_2    by all same airline, aircraft, airport.

What I want is such a result set:

Arrival  Departure
-------  ---------
Arr_1    .....
Arr_2    Dep_1
.....    Dep_2

Therefore, the select first finds a departure for an arrival (DX for A). Then, to verify that DX is the correct departure for A, it bactracks an arrival for the DX (AXX). If A and AXX are the same, we have found what we are looking for. A similar approach for D also in the select.

The select cannot be wrong (by means of a typography) because it runs well in prevoius versions (and in the production with hundreds of users).

3. PFILandedCompany: It is a shared flight info db which can be used by many companies. This table holds the interested companies in the landing.

Some notes:
a) To generate the test db, don't forget to change the database to test, I didn't include "use test" statement.

b) I didn't create the foreign key constraints since I don't believe the case is related with them.

Best Regards,
Gokhan Demir
