Main page
Typical errors at solution of exercises. Exercise 49
S. Moiseenko
Among the passengers, who flew by the planes of more then one company, find those who made
the same number of trips by the planes of each of these companies. Result set: passenger name.
This task generates a big number of wrong solutions, which I further subdivide into
two groups.
The first type of error is caused by misinterpreting of the task. For example, some
visitors try to identify two passengers, who would have made equal number of flights
with two or more airlines.
I want to make it clear, that it is necessary to consider an individual passenger
and to count the number of flights, he has made with each of the airlines, the services
of which he has used.
What comes next? Let’s consider an example from the second group of wrong solutions:
SELECT DISTINCT name
FROM (SELECT id_psg, id_comp, count(pt.trip_no) as CNT
FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no
GROUP BY id_comp,id_psg)a,
(SELECT id_psg, id_comp, count(pt.trip_no) as CNT
FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no
GROUP BY id_comp,id_psg)b,
passenger p
WHERE a.id_psg=b.id_psg and a.id_comp<>b.id_comp and a.cnt=b.cnt
and p.id_psg=b.id_psg
I want to point it out, that the erroneous DISTINCT name eliminates the possibility
of taking namesakes into account from the start. Although that’s not the most important
thing about it.
Two identical queries are joined in FROM clause
SELECT id_psg, id_comp, count(pt.trip_no) as CNT
FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no
GROUP BY id_comp,id_psg
As it is mentioned above, they are aimed to count for each passenger the number
of flights, he has made with the planes of each company.
These queries are joined presuming that:
- the passenger is the same;
- the companies are different;
- the number of flights coincides.
Thus, if the passenger has made 3 flights, say, with Aeroflot Company and 3 flights
with Don_avia Company, such a passenger satisfies the terms of such a joining and
his name will be returned within the results of such a query. If the passenger has
used only two companies, it is the correct result. But
what if he has used three
companies?
If the result of the aforementioned subquery is
Bruce Willis Don_avia 2
Bruce Willis Aeroflot 2
Bruce Willis Dale_avia 1
then the passenger Bruce Willis does not correspond to the terms of the task, although
the query under consideration will print it out, because two first lines are joined
in the query.
Thus, the number of flights of a passenger with each of the companies, the services
of which he has used, must be in proportion 1:1:...:1
Main page