Exercise #37 page 3
Look now, how we can more easily write a query that contains a similar mistake:
The idea: Ship classes from the Ships table are selected in the subquery and merged with the leading ships of the Outcomes table with preservation of duplicates (UNION ALL). This uses the fact that the name of the leading ship coincides with the class name (SELECT Ship) (!!!). The fact that the duplicates are stored is absolutely correct, because we get one row for a class for any number of ships in this class otherwise. Then grouping by class, and picking the classes that contain a single ship are being done. The solution looks much shorter and clearer than the solution 3.2.2. Correction it will be easier, but will have to be corrected, because the solution will give an incorrect result, if the leadIng ship is present both in the Ships table and in the Outcomes table, with the result that we count it twice.
It is worth paying attention to the fruitful idea of this decision - first to combine all the ships, and only then carry out the grouping by classes.