Another SQL puzzle

Time And Date
Image by mobology via Flickr

Once more from which seems to have a phase-of-the-moon related availability.

Database is defined as:

Database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, ID_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
Company table has ID and name of the company, which transports passengers. Trip table has information about trips: trip number, company ID, plane type, departure city, arrival city, departure time, and arrival time. The passenger table has passenger’s ID and passenger’s name. Pass_in_trip table has information about the flights: trip number, departure date (day), passenger’s ID and his place during the flight. We should note that,
– Any trip is being accomplished every day; duration of a flight is less than a calendar-day (24 hours);
– Time and date are considered comparatively one time zone;
– The departure time and the arrival time are given to within a minute;
– There can be the passengers bearing the same names (for example, Joe Bloggs);
– The place during the flight is a number followed by a letter; the number defines the row number, the letter (a – d) – the place in the row (from the left to the right) in the alphabetical order;
– Relationships and restrictions are shown in the data schema.

And the question is:

Among the clients which only use a single company, find the different passengers who have flown more often than others. Result set: passenger name, number of trips, and company name.

So, I have this SQL:

SELECT, COUNT(T.trip_no) AS journeys,
Passenger P, Trip T, Company C, Pass_in_trip PIT
P.ID_psg = PIT.ID_psg AND PIT.trip_no = T.trip_no
AND T.ID_comp = C.ID_comp
Passenger, Trip, Company, Pass_in_trip
WHERE Pass_in_trip.ID_psg = P.ID_psg AND Pass_in_trip.trip_no = Trip.trip_no
AND Trip.ID_comp <> C.ID_comp)

Which correctly, as far as I can see, outputs the names of clients who use only one company, the number of trips they have made and the company name.

But how do I limit that to the MAX?

I have tried adding “HAVING MAX(COUNT(T.trip))” but the RDMS objects – telling me I am trying to evaluate something that does not give a Boolean answer as a Boolean.


3 responses to “Another SQL puzzle”

  1. I don’t think you need Passenger and Company in the FROM clause of the nested query.

    HAVING(MAX(…)) probably gorks because MAX() returns a value. Assuming you are looking for the most frequent flier dedicated to each company (the wording of the task was slightly fuzzy), I think you need to nest your two level query inside yet another query, which selects name and journeys from those in the current query having journeys = MAX(journeys), grouped by company.

  2. You ought to charge 🙂

    You are right about the FROM clause, they are left over from my initial scribbles – I had a longer where clause which, as I was typing I realised could be shorter but plainly didn’t clean it all up.

    I guess you are right about the third level of query nesting. I could see that was a possibility before but thought I was probably missing a simpler way to do this.

    My exam is tomorrow, so hopefully I won’t be looking for your help on this stuff for much longer 🙂

  3. […] This SQL works for the problem posed below: […]

%d bloggers like this: