Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Question Unanswered: SQL SELECT please help

    hello,
    i have a really confusing problem, and i cant seem to get the recordset i want no matter what SELECT i try.

    I have an airline flight database. What i want to do is pull the cheapest price for a particular city for each day of the year.

    im thinking this will requiere a correlated subquery but im not sure.

    any suggestions?

    thanks in advance,
    Jonah

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using MS-SQL, you could use:
    PHP Code:
    SELECT Convert(CHAR(8), flightDate121)
    ,  
    Min(fare)
       
    FROM fares
       GROUP BY Convert
    (CHAR(8), flightDate121)
       
    ORDER BY 1 
    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Red face thanks, but its a little more complicated

    i guess i didnt explain myself right.
    What i want to pull is the cheapest flight for ALL dates in a month (since i am binding this to a price schedule calendar)
    So if the cheapest flight to London on april 28th is a 3 month fixed ticket for 600 dollars with American Airlines, it will return those values, but if the cheapest price to the same city on april 29th is a 6 month open ticket with United Airlines for 700 dollars, then i want those values for that date.

    Its very complicated i know, but do you see what i mean?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nope, I'm a lot more confused than when I started!

    Are you looking for the best price on a given class of ticket for a given month? So for every month you'd have a walk-up price, a 14 day price, a 30 day price, a 90 day price, etc? How do you want to handle ties (where two airlines offer exactly the same price on a given ticket)?

    Without worrying about the code, can you explain in English what you want to show, or better yet post a fabricated example set of data with the results that you'd like to see? If you can give data, keep in mind that columns are really tough to do on web pages, so commas usually make good separators!

    -PatP

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    This question reminds me of a very similar question asked about 2 months ago.... is this a class/test question or something??

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Angry ??

    can you give us a description of the table(s) please?

    And rephrase your question.

    and WHERE did our avatars go??

    ~Le

  7. #7
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post one more time

    i sincerely apologize for the confusion. here is some sample data and what i want:

    city airline date price type

    Tokyo UA 2004/04/01 $600 1monthFIX
    Tokyo JL 2004/04/01 $600 1monthFIX
    Tokyo NW 2004/04/01 $480 21daysFIX
    Tokyo UA 2004/04/02 $550 1monthFIX
    Tokyo JL 2004/04/02 $600 1monthFIX
    Tokyo NW 2004/04/02 $580 21daysFIX
    Tokyo UA 2004/04/03 $500 1monthFIX
    Tokyo JL 2004/04/03 $500 1monthFIX
    Tokyo NW 2004/04/03 $500 21daysFIX

    So i need to produce something where the 1st of april will return:
    Tokyo NW 2004/04/01 $480 21daysFIX

    The 2nd of april will return:
    Tokyo UA 2004/04/02 $550 1monthFIX

    And the 3rd of April will return any 1 of the 3 records (doesnt matter which).

  8. #8
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post

    this is what i tried, but it didnt work for airlines that had more than one flight on the same day.

    "SELECT DISTINCT date, city, airline, Min(price) AS minprice FROM airprices WHERE airline = (SELECT TOP 1 airline FROM airprices WHERE city = '" & request.QueryString("city") & "' AND price = (SELECT Min(price) FROM airprices WHERE cityID = '" & request.QueryString("city") & "')) AND city = '" & request.QueryString("city") & "' GROUP BY date, city, airline "

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is hideously ugly, but it works. I need to think through a better solution, but for now I'd start with:
    Code:
    "SELECT d.city, d.airline, d.[date], d.price, d.type
       FROM (SELECT DISTINCT city, [date] FROM airprices) AS a
       JOIN (SELECT b.city, b.airline, b.[date], b.price, b.type
          FROM airprices AS b
          WHERE  b.price = (SELECT Min(c.price)
             FROM airprices AS c
             WHERE  c.city = b.city
                AND c.[date] = b.[date])) AS d
          ON (d.city = a.city
          AND d.[date] = a.[date])
       WHERE  d.airline = (SELECT Min(e.airline)
          FROM airprices AS e
          WHERE  e.city = d.city
             AND e.[date] = d.[date]
             AND e.price = d.price)
           AND cityID = '" & request.QueryString("city") & "')) AND city = '" & request.QueryString("city") & "'"
    -PatP

  10. #10
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    wow

    thats seems really complicated, i thought there would be an easier solution.
    ok, so i just tried it and got a syntax error on the FROM clause. ill keep working on it.
    thanks for your help so far,
    Jonah

  11. #11
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Thumbs up simplified

    well, i couldnt figure it out, so i eneded up dropping the airline from the query altogether. this is working for me now:

    "SELECT DISTINCT B.date, B.city, (SELECT Min(price) FROM airprices A WHERE city = '" & request.querystring("city") & "' AND A.date = B.date) AS minprice FROM airprices B WHERE city = '" & request.querystring("city") & "'"

    although this wont return the airline, it will return the cheapest price of all the flights for each individual date in a month. then if i want to get the airline i just query the database using the date, price, and city for a particular flight and do a Min(airline) on it.

Posting Permissions

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