Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    Question Unanswered: If I only had a brain SQL

    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.

    Please help if you can.
    the light is on, someone is home, but they dont know they are. HELLO!

  2. #2
    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. #3
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    Re: If I only had a brain SQL

    Hi there thanks for replying


    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.
    the light is on, someone is home, but they dont know they are. HELLO!

  4. #4
    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. #5
    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
    the light is on, someone is home, but they dont know they are. HELLO!

  6. #6
    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. #7
    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
    the light is on, someone is home, but they dont know they are. HELLO!

  8. #8
    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. #9
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Yes thats right
    the light is on, someone is home, but they dont know they are. HELLO!

  10. #10
    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. #11
    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
    the light is on, someone is home, but they dont know they are. HELLO!

  12. #12
    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
    the light is on, someone is home, but they dont know they are. HELLO!

Posting Permissions

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