Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2005
    Posts
    1

    Unanswered: Nested queries difficulty

    Hi!
    I'm a SQL beginner struggling with some SQL textbook examples, and i'm stuck at one exercise.
    Can anyone give me a hint??

    Ok, here it goes:
    I'm trying to simulate a airport booking system, where CUSTOMER makes a RESERVATION to a FLIGHT. The FLIGHT have a ROUTE, a AIRCRAFT, and some CABINSTAFF. Plus some other minor attribures..

    The question i'm trying to answer now is "how many seats are there left on all flights having a specific route?". I can get how many seats the airplanes have with this:

    SELECt SEATS
    FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
    WHERE FL.FNUMBER = R.FLIGHTNUMBER
    AND FL.AIRCRAFT = AC.NAME
    AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
    AND "DATE" = '2005-03-01';

    It will return:
    100
    200
    (there are two flights that match the selected route and date, one airplane have 100 seats, the other one 200).

    I then find out how many customers that are booked on those flights:

    SELECT COUNT(RESERVATION_NO)
    FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
    WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
    AND RN.FNUMBER = R.FLIGHTNUMBER
    AND F.FNUMBER = RN.FNUMBER
    AND F.AIRCRAFT = A.NAME
    AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
    AND FDATE = '2005-03-01'
    GROUP BY RN.RESERVATION_NO

    It will correctly return:
    1
    2

    Now i want to substract the second query from the first to get the number of seats left in those flights:

    SELECT FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
    FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
    WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
    AND RN.FNUMBER = R.FLIGHTNUMBER
    AND F.FNUMBER = RN.FNUMBER
    AND F.AIRCRAFT = A.NAME
    AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
    AND FDATE = '2005-03-01'
    GROUP BY RN.RESERVATION_NO)
    FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
    WHERE FL.FNUMBER = R.FLIGHTNUMBER
    AND FL.AIRCRAFT = AC.NAME
    AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
    AND "DATE" = '2005-03-01';

    But that does'nt work since you can't substract a set, only a single row, right?? So how do i fix this??
    The answer should be:
    219
    98

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Without getting into the details of your query, you need to correlate the subquery to the main query something like this:

    Code:
    SELECT FL.FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
                                     FROM   FLIGHT F, ...
                                     WHERE  ...
                                     AND    F.FLIGHTNUMBER = FL.FLIGHTNUMBER)
    FROM   FLIGHT FL, ...;

Posting Permissions

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