1. Registered User
Join Date
Aug 2003
Location
UK
Posts
78

Hello everyone,

I need some help with SQL
The problem:- I need to display a group of records from a database. These records are flights. The flights are multi-stop i.e. Go from A to F via B, C, D, E. Each flight has a FlightNumber and each sector (i.e. C to D) has a SectorNum. The fields in the database are:- DeparturePoint, FlightNum, SectorNum, ArrivalPoint (loads of other fields but not important for this query).
For a journey from C to F
My Logic:-
Step 1:- Get all the flights that depart C and get the FlightNums and SectorNums
Step 2:- Get all the flights that arrive at F and get the FlightNums and SectorNums
Step 3:- Get the flights from/to both that have the same FlightNums (i.e. goes from one to the other, but not necessarily in the right direction) and get the flights which have a SectorNum BETWEEN departure SectorNum AND arrival SectorNum (i.e. going in the right direction)

The way I have been doing it is:-
Do step 1 above and put the results in a listbox (list1)
Then do step 2 put the results in another listbox (list2)
Then go through list1 and compare the flights in list2 if they match I get the FlightNum departure SectorNum and arrival SectorNum

How do I do this in SQL I’ve been trying for three days now and I’m getting desperate.

2. Registered User
Join Date
Sep 2003
Location
Lancashire, UK
Posts
22

## Re: If I only had a brain SQL

Do you think you could add a small set of example data to show exactly what you require.
I don't know if its just me but the SectorNum bit confused me

Paul

3. Registered User
Join Date
Aug 2003
Location
UK
Posts
78

## Re: If I only had a brain SQL

Each flight has a sector number which is sequential i.e.

A to B FlightNum = 1443 Sectornum = 1
B to C FlightNum = 1443 Sectornum = 2
C to D FlightNum = 1443 Sectornum = 3
D to E FlightNum = 1443 Sectornum = 4
E to F FlightNum = 1443 Sectornum = 5
For a flight from B to E
SELECT * FROM flights WHERE flightNum = 1443 AND SectorNum BETWEEN 2 AND 4

i.e. sector 1 is the flight before I get on the plane and sector 5 is the flight after I get off.

4. Registered User
Join Date
Sep 2003
Location
Lancashire, UK
Posts
22
Bear with me on this, I'll get it eventually

Each record is a flight for one sector
A flight is made up of multiple records, one for each sector it travels along

Is the leg between A and B always sector 1 for all flights or is the first leg of a flight always sector 1 irrespective of where it is from and to?

Must a flight always travel from A -> B -> C -> D -> E -> F or can it go A -> D -> E -> F?

Paul

5. Registered User
Join Date
Aug 2003
Location
UK
Posts
78
Hi
It is always A -> B -> C -> D -> E -> F
A->B is always sector 1

Thanks

6. Registered User
Join Date
Sep 2003
Location
Lancashire, UK
Posts
22
Sorry to keep asking question, i know you just want an answer
Should be last one then I will have it

Is the sector number for a flight the same irrespective of the direction ie a flight from B to C and a flight from C to B are both sector 2?

Also, is there a table containing all sector numbers and their corresponding end destinations, as I feel their will need to be one in order to produce the results you need in a query.

Paul

7. Registered User
Join Date
Aug 2003
Location
UK
Posts
78
Thanks for your time. It's me I am bad at explaining what I am thinking

All sectors have their own entry in the db. If it is the first sector from any airport then the sector number is 1
eg:
flightno '1234' from Scunthorpe to Bognor via Manchester, Tring and st Albans (not really airports but more fun to imagine. ops except manchester)

fltNo '1234' from Scunthorpe to Manchester is sector 1
fltNo '1234' from Manchester to Tring is sector 2
fltNo '1234' from Tring to StAlbans is sector 3
fltNo '1234' from StAlbans to Bognor is sector 4

fltNo '9876' from Bognor to StAlbans is sector 1
fltNo '9876' from StAlbans to Tring is sector 2
fltNo '9876' from Tring to Manchester is sector 3
fltNo '9876' from Manchester to Scunthorpe is sector 4

8. Registered User
Join Date
Sep 2003
Location
Lancashire, UK
Posts
22
That makes sense now
So basically you need to find all flights which at some point depart from some airport say called DepartureDestination and at some point arrive at some airport say called ArriveDestination and the sector departing from DepartureDestination is less than the sector arriving at ArriveDestination
Does that sound right?

9. Registered User
Join Date
Aug 2003
Location
UK
Posts
78
Yes thats right

10. Registered User
Join Date
Sep 2003
Location
Lancashire, UK
Posts
22
The following will list all flights that fly from DepartureDestination to ArriveDestination

Need a query say qryDepartures as follows
"SELECT FlightNum, SectorNum FROM MainTable WHERE DepartuePoint = DepartureExpression"

Need a second query say qryArrivals as follows
"SELECT FlightNum, SectorNum FROM MainTable WHERE ArrivalPoint = ArrivalExpression"

Third query say qryFlights as follows
"SELECT qryDepartures.FlightNum, qryDepartures.SectorNum As DepartSectorNum, qryArrivals.SectorNum As ArriveSectorNum FROM qryDepartures INNER JOIN qryArrivals ON qryDepartures.FlightNum = qryArrivals.FlightNum WHERE qryDepartures.SectorNum <= qryArrivals.SectorNum"

Final query should give the results
"SELECT * FROM MainTable INNER JOIN qryFlights ON MainTable.FlightNum = qryFlights.FlightNum WHERE SectorNum >= DepartSectorNum AND SectorNum <= ArriveSectorNum ORDER BY FlightNum, SectorNum"

DepartureExpression and ArrivalExpression are expressions that could be parameter expressions eg "[Enter the departure destination]", or a expression refering to a control on a form eg "[Forms]![frmFlightSearch]![cboDestination]", etc.

I haven't actually tested this, this was just off the top of my head on the fly, so if it doesn't do as expected let me know

Paul

11. Registered User
Join Date
Aug 2003
Location
UK
Posts
78
Hi Paul,
Thanks for your time on this one. Sorry for the late reply, I’ve been away. It works! But it has opened a hole in my logic, but at least I’m a stop closer.
1. One flight in my database starts in the same place as it ends ie a circular route via 4 airports.
2. Also I have found a flight that has the same flight number as another flight!!

In the data I have in my main table there is also a flightOccurrenceNo

The ‘flight occurrence number’ will, I think, make each flt unique.

Eg

From To flightOccurrenceNo Sectornum FltNum
LHR MAN 01 01 1234
MAN BHX 01 02 1234
BHX GLA 01 03 1234
GLA LHR 01 04 1234

LHR MAN 01 01 1234
MAN BHX 02 02 1234
BHX LHR 03 03 1234

So:- From + flightOccurrenceNo + Sectornum +FltNum = unique?

I think that should do it? What do you think?

Can you recommend a book to further my SQL knowledge

12. Registered User
Join Date
Aug 2003
Location
UK
Posts
78
Hi Paul,
I have cracked it!

SELECT *
FROM TblSsim INNER JOIN qFlights ON TblSsim.FltNo = qFlights.FltNo
WHERE (((TblSsim.FltOccurrenceSectorNum)>=[DepartSectorNum] And (TblSsim.FltOccurrenceSectorNum)<=[ArriveSectorNum]) AND ((qFlights.qTo.FltOccurenceNum)=[tblSSim].[FltOccurenceNum]));

Thank you

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•